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.
I mentioned this in another comment, but Learn to Rank allows a customized sorting order that is specified by an administrator. Where you see this is in systems where you need a really fast evaluation (ms) and computation doesn't work well.
For example, in an e-commerce system you might want search results that reflect what your users are likely to buy. For a few items you might be able to do something computationally with SQL, but at scale there are lots of features and signals and noise and it quickly moves past algebra.
Are there times when limiting oneself to "simple" SQL-only solutions is bad? Is this one of those times? Feels like "yes" to both questions here.
Edit: (here's the previous post from hacker news with the most comments)<https://news.ycombinator.com/item?id=16635440>
I am coming from the search world, but databases were my first experience in the industry.
It would be interesting to see some things like Learn To Rank or semantic reranking become a part of SQL. These have real impacts on accuracy with generative AI.
You are not[1]. This is not a complex problem but the author has managed to make a blog article out of it and come to the conclusion that rational numbers are required.
The reason the author gives for rejecting your proposal is they have added a uniqueness constraint which makes the update awkward but you can indeed work around that in a variety of ways or just drop the uniqueness constraint altogether.
[1] Although update doesn't have an "order by" clause[2] but I'm assuming you mean 'update' and then subsequently 'select' with the order by. I think if you actually want an update to proceed sequentially in some order you have to use a cursor.
