User-defined Order in SQL

Having run into this problem a few times now, Approach 1 is unfortunately best. Two reasons: (1) If updates fail, you don't get a resulting broken state (2) The size of the lists is not that large (<50), so the extra writes are not that expensive. Essentially, with any variation on fractional updates (where only 1-2 rows are updated per list update), the client is sending to the server "move item a to position x". Then, shortly after, "move item…

There's a no-free-lunch theorem here that's being ignored. If you have a space of 64 bits to represent elements of some ordered field, and you insert items one at a time, there is some ordering of the items that allows you to make only 64 inserts. Claiming that numbering the items with integers only allows you 16 insertions, but "It would take virtually forever to run out of space through repeated list reordering" using rational numbers just can't be correct…

Whenever I see this discussion come up my question is always: Why do you want to manually sort a giant list? I have never needed this. For smaller lists at the human scale (<1000 items), just use an integer. For larger lists no human should have to manually sort those anyway. Find some computational way of presenting the data to the human.