MS SQL - Manual Order By


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,


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You need to add this field.

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.
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


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.

Scott PletcherSenior DBACommented:
>> Example if user want to add a new document in the middle.  Can I avoid to have to update every document? <<

I would think so.  Just do your initial numbering in 100 (or 1000) increments.  You can then add / move items w/o having to renumber the whole list.  You can use ROW_NUMBER to generate a true sequential number for the user to see, while your code uses the "hidden" numbers actually stored in the table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

CboudrozAuthor Commented:
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

order by
1.5 -- new doc
1.75 -- new doc

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...

Ephraim WangoyaCommented:

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.
Scott PletcherSenior DBACommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.