Cboudroz
asked on
MS SQL - Manual Order By
Hi,
I'm working whit a Web application (.net) and we have user that have folder, the folder are used for saving document (folder and document are only row in tables).
We have functionality that will allow you to choose the order by (ex: insert date, type, ...).
Now we want to add the possibility to make manual order by.
First I was thinking about adding a column in the document table "iOrderBy_id", but I'm concern about the complexity of the modification made to the order by. Example if user want to add a new document in the middle. Can I avoid to have to update every document?
Any suggestion will be appreciated,
thanks,
I'm working whit a Web application (.net) and we have user that have folder, the folder are used for saving document (folder and document are only row in tables).
We have functionality that will allow you to choose the order by (ex: insert date, type, ...).
Now we want to add the possibility to make manual order by.
First I was thinking about adding a column in the document table "iOrderBy_id", but I'm concern about the complexity of the modification made to the order by. Example if user want to add a new document in the middle. Can I avoid to have to update every document?
Any suggestion will be appreciated,
thanks,
hi Cboudroz
if you are going to add a column that is mainly for the order by, then you will have to give the user the option to move items up or down.
just like if you create a dropdownlist box in a .net application, you can move items up and down, by clicking on the arrows
and yeah you will have to always send back the order by ids , because if one changes it means all the rest will change, and you will have to update
e.g
item2 orderid1
item3 orderid2
item1 orderid3
if you move item2 one step down it means you will have to update item2 and item3 orderids and so on.
order-by.png
if you are going to add a column that is mainly for the order by, then you will have to give the user the option to move items up or down.
just like if you create a dropdownlist box in a .net application, you can move items up and down, by clicking on the arrows
and yeah you will have to always send back the order by ids , because if one changes it means all the rest will change, and you will have to update
e.g
item2 orderid1
item3 orderid2
item1 orderid3
if you move item2 one step down it means you will have to update item2 and item3 orderids and so on.
order-by.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Should It be better to take a float? I'm nervous that 2 years later a user end up whit two document in order 1500 and 15001.
order by
1
2
3
order by
1
1.5 -- new doc
1.75 -- new doc
2
3
order by
1
2
3
order by
1
1.5 -- new doc
1.75 -- new doc
2
3
don't use float. use decimal with the number of decimals you want but is the same to use without decimals...
You allwais can get a point you can't insert so you chuold reditribuite all the values to continue...
You allwais can get a point you can't insert so you chuold reditribuite all the values to continue...
If you want the user to sort by their own selected field (insert date, type etc), you don't need to add a column to the table.
You only need a table to store each users sort field then when when creating your query, read the field from taht table and use it to sort in the ORDER BY clause, if there is no sort field defined for a user, use a default field eg the primary key field.
No, under no circumstances should you ever use a float. You could use decimal, although there's really no need for that.
Techicallly you should have code available that renumbers a specific key value's rows if needed. If, for example, for some reason someone needs to add 2000 rows between rows. I suspect it would be very, very rare that you would ever need that code.
Techicallly you should have code available that renumbers a specific key value's rows if needed. If, for example, for some reason someone needs to add 2000 rows between rows. I suspect it would be very, very rare that you would ever need that code.
You can increment the order by 2^something value (for example 16384) and each time you insert a value in the middle, you calculate the average and use the value.