What's the best way to store sort order in SQL?

Question

The guys at the top want sort order to be customizable in our app. So I have a table that effectively defines the data type. What is the best way to store our sort order. If I just created a new column called 'Order' or something, every time I updated the order of one row I imagine I would have to update the order of every row to ensure posterity. Is there a better way to do it?

Answer 1

You can use a float instead, and as long as you have enough precision you can always just set ordinal column for the moved record to the midpoint between the records on either side.

Answer 2

Generally the application would add the approriate ORDER BY clause to the query. If the result sets to be sorted are relatively small you can have keys on the selection criteria. Even with large results it is often better to sort the selected data than retrieve in order by index.

If the requirement is to have orders like B A Z T Q M K, then you will need a column to place the relative order into. The appropriate value would need to be determined each time you add a row. However, this works well for code tables which are relatively static.

Answer 3

Use an int field. When you update the sort order of one row, you only have to update the field on the row you're updating and any rows between the row's old and new positions. This means that swapping two rows only involves touching those two rows. Also, for rows you're updating that aren't your "active" row, you only need to increment or decrement the field; the queries are easy to write.

Answer 4

None of the answers so far have touched on the real problem with custom sort order and that is what happens when two different people want the same records sorted differently.

If you need a custom sort order, you need a related table to store it in, not an additional field. The table would have the userid, the recordId of the data and the sort order for the record. That way Joe Smith can have one order and Sally Jones another for the same data. Now you have the problem of new records being added to the data set. Do you put them at the beginning of the sort order or the end or do you require the person to set an order for them before they can be added to the set. This is in actuality a very complex porblem that is generally not worth the amount of time it takes to implement becasue almost no onne ever uses that system once it's inplace (I mean do I really want to go through a hundred records and mark the individual order of each one?). Now it gets complicated interms of saving the order of all the records (which will of course require changes the next time the query is run since there will be new records.) This is very painful process of limited untility.

I did this once in a proposal writing appliction because we needed to be able to sort the parts and tasks on the propoasal in the order we thought woudl be most impressive to the customer. Even then, we had to institute a default order, so that they only neede to move around teh two or three things they really wanted to show up first instead of ordering 10,000 individual parts.

A better choice if you can get them to buy off on it, is to allow them to sort the data by columns (desc or asc). Usually the user interface can be designed so that if you click on a column header, it will resort the data by that column. This is relatively straightforward to do and meets most needs for custom ordering.

You really need to discuss this requirement with management and get details of how they want it to work beyond, I want custom ordering. This is often one of those things people think they want, but don't really use.