Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 898
Anthony Avatar asked May 13 '10 01:05

Anthony


People also ask

How do you optimize orders in SQL?

From a b-tree index that you have on the table or the order of a result set in a subquery. In a slow scenario you do not have that predefined order, and MySQL has to implicitly put all data into a temporary table, sort the table on some field and return the n rows from your LIMIT clause.

Is SQL ORDER BY stable sort?

The ORDER BY clause contains a column or combination of columns that are guaranteed to be unique. The simplest way to understand that a sort is not stable is to go back to the definition of a table. Tables are inherently unordered in SQL. So, there is no ordering to fall back on for "stability".

How do I sort by order in SQL?

The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns. By default ORDER BY sorts the data in ascending order. We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.

Does indexing improve sorting?

Using the indexes can improve the performance of the sorting operation because the indexes create an ordered structure of the table rows so that the storage engine can fetch the table rows in a pre-ordered manner using the index structure.


2 Answers

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.

like image 71
HLGEM Avatar answered Oct 21 '22 23:10

HLGEM


The basic algorithm might be like one described below. Initially the sort field varies from item to item by 1000 (you may consider another interval). The items in the table are in ordered state just for the sake of simplicity. Btw, I've create Yii2 component to manage this stuff. And this one if you need a sortable tree sortable tree.

id | sort
---+-----
1  | 1000
---+-----
2  | 2000
---+-----
3  | 3000
---+-----

Lets imagine we are going to add an item (id 4) after id 1:

id | sort
---+-----
1  | 1000
---+-----
4  | 1500
---+-----
2  | 2000
---+-----
3  | 3000
---+-----

So to calculate sort value for id 4 we took the sort value of the item before, which is 1000 and the item after - 2000 and took the mean. If you get a float, just round it to the nearest integer. If you need to insert an item at the beginning of the list, then you take a mean of (1000 and 0, which is 500).

Now, if we need to insert an item (id 5) after id 1, we do the same:

id | sort
---+-----
1  | 1000
---+-----
5  | 1250
---+-----
4  | 1500
---+-----
2  | 2000
---+-----
3  | 3000
---+-----

Later on, you might face to this scenario:

id | sort
---+-----
1  | 1000
---+-----
15 | 1001
---+-----
...
---+-----
5  | 1250
---+-----
...
---+-----

So if you need to insert an item (id 16) between 1 and 15, first you should increment sort field by 1000 of all items followed by 1:

id | sort
---+-----
1  | 1000
---+-----
15 | 2001
---+-----
...
---+-----
5  | 2250
---+-----
...
---+-----

Now you can insert the item (id 16):

id | sort
---+-----
1  | 1000
---+-----
16 | 1501
---+-----
15 | 2001
---+-----
...
---+-----
5  | 2250
---+-----
...
---+-----
like image 16
Sergey Onishchenko Avatar answered Oct 22 '22 00:10

Sergey Onishchenko