What's the Best Way to Store Sort Order in SQL

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

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 problem that is generally not worth the amount of time it takes to implement because almost no one ever uses that system once it's in place (I mean do I really want to go through a hundred records and mark the individual order of each one?). Now it gets complicated in terms 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 application because we needed to be able to sort the parts and tasks on the proposal in the order we thought would be most impressive to the customer. Even then, we had to institute a default order, so that they only need to move around the 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.

What is the most efficient way to store a sort-order on a group of records in a database?

What about just using an integer column which defines the order? By default you assign numbers * 1000, like 1000, 2000, 3000.... and if you move 3000 between 1000 and 2000 you change it to 1500. So in most cases you don't need to update the other numbers at all. I use this approach and it works well. You could also use double but then you don't have control about the precision and rounding errors, so rather don't use it.

So the algorithm would look like: say you move B to position after A. First perform select to see the order of the record next to A. If it is at least +2 higher than the order of A then you just set order of B to fit in between. But if it's just +1 higher (there is no space after A), you select the bordering records of B to see how much space is on this side, divide by 2 and then add this value to the order of all the records between A and B. That's it!

(Note that you should use transaction/locking for any algorithm which contains more than a single query, so this applies to this case too. The easiest way is to use InnoDB transaction.)

What would be the best way to store records order in SQL

The best solution is one which mirrors functionality, and that's a simple list of integers. Keeping the list in order is only a few SQL statements, and easier to understand than the other solutions suggested (floats, gapped integers).

If your lists were very large (in the tens of thousands) then performance considerations might come into play, but I assume these lists aren't that long.

Best way to store sort order/priority?

I ended up following the advice in this question: https://stackoverflow.com/a/6804302/731052

I set the sort-order column = id * 1000 so I could get unique orders. So far this works very well and haven't had any problems with it.

Using a sort order column in a database table

Update product set order = order+1 where order >= @value changed

Though over time you'll get larger and larger "spaces" in your order but it will still "sort"

This will add 1 to the value being changed and every value after it in one statement, but the above statement is still true. larger and larger "spaces" will form in your order possibly getting to the point of exceeding an INT value.

Alternate solution given desire for no spaces:

Imagine a procedure for: UpdateSortOrder with parameters of @NewOrderVal, @IDToChange,@OriginalOrderVal

Two step process depending if new/old order is moving up or down the sort.

If @NewOrderVal < @OriginalOrderVal --Moving down chain 

--Create space for the movement; no point in changing the original 
    Update product set order = order+1 
    where order BETWEEN @NewOrderVal and @OriginalOrderVal-1;

end if

If @NewOrderVal > @OriginalOrderVal --Moving up chain

--Create space  for the momvement; no point in changing the original  
  Update product set order = order-1 
  where order between @OriginalOrderVal+1 and @NewOrderVal
end if

--Finally update the one we moved to correct value

    update product set order = @newOrderVal where ID=@IDToChange;

Regarding best practice; most environments I've been in typically want something grouped by category and sorted alphabetically or based on "popularity on sale" thus negating the need to provide a user defined sort.

Store data sorted into new table

SQL does not retain the sort.

This is soooo true. SQL tables represent unordered sets. No ordering. Get it?

That said, you can sort of impose order on the data. Here are two methods:

1. You can define a primary key. The data is physically sorted on the data pages (in SQL Server) based on the primary key.
2. You can define an identity column. In conjunction with order by, the identity column will capture the ordering.

Strictly speaking these have no effect on the result set from a query on the table. Instead, they are methods for storing the ordering in the data.

What is true about tables being unordered is also true about result sets. So if you run:

Select MemberID, TotalSpend
from SampleTable;

The result set is unordered. That means that even with a primary key or an identity column or both, SQL Server can return the rows in whatever order it so desires. It can even return the data in different orders on different runs.

Fixing this is easy. If you want the data in a particular order, simply add the following to your query:

order by TotalSpend 

If you have an index on the column, then the sorting would normally use the index (unless the data is very small), so very little overhead would be incurred.

How to keep ordering of records in a database table

I feel this is related to the general problem of using an array vs a linked list.
How about storing a foreign key referencing the next record in the same table? This is the linked list like approach.

For your example there are not too many tabs so an array based approach should work fine.
But for someone having hundreds of records it may be useful to use a self-referential foreign key.

ID Name      NExT  
 1 Home      2  
 2 About     3  
 3 Products  4 
 4 Shopping  NULL

Adding and deleting rows will be akin to linked list insertion and deletion.

Update:
Modified table

ID Name       NExT  
 1 Home       5  
 2 About      3  
 3 Products   4 
 4 Shopping   NULL
 5 Contact us 2

The order will be 1 > 5 > 2 > 3 > 4 as determined by the next column.
You can also use a prev column which will make it similar to a doubly linked list.

---

Related Topics