This question already has an answer here:

Say we have a column in table or a property in a collection called "sortOrder" which simply is an integer which denotes which order it is to appear when displayed.

I am trying to figure out the simplest way to implement this, considering that when we insert a new row in the table it may have a sort order which bisects the current list - so then I guess we'd have to update all the rows where the sortOrder field value is greater than or equal to the newly inserted item.

Has anyone had this problem, how did you solve it?

Here is a question that illuminates the problem if you don't get it:


marked as duplicate by gnat, amon, Bart van Ingen Schenau, Christophe, 8bittree Dec 5 '17 at 19:07

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • True beauty can never be duplicated – user286798 Dec 5 '17 at 9:30

After reading the related question mentioned in the comments, I guess I understood your problem.

You could insert a new item at the end of the list, assigning it to some "sortOrder" that represents the last position.

After that, you implement some feature for changing the order of the items, by enabling the user to move each one either "up" or "down" (same as the accepted answer of the related question mentioned); this is easily done and will require only a swap between 2 items.


It's almost always a bad idea to store a sort order persistently.

In order to be useful in a user interaction, there must be an obvious reason for things being in some specific order. That reason is almost certainly going to be easily calculable, and it will be easier to retrieve things as they are and sort them before displaying than to bother maintaining the sort order with every change in the collection. There might be circumstances where caching the order like this makes sense (e.g. huge number of records, rare insertions, no changes to the sort-relevant fields, efficient database and slow-running client device), but I can't think of a real-life example.

  • 2
    This answer isn't incorrect, but it doesn't answer the question. Storing an explicit sort order is necessary when the order is defined extrinsically, e.g. manually by a user. Example: order of widgets on a website's dashboard. – amon Dec 5 '17 at 8:52
  • 2
    Right, @amon is correct - there is no other field in the table where we can interpret the correct sort order - the integer field is the only field that has the information, otherwise it would be arbitrary. Therefore, the sort order is not "easily calculable" from other fields in the table. – user286798 Dec 5 '17 at 8:54