User-defined Order in SQL (2018) | Hacker News Reader

User-defined Order in SQL (2018)

begriffs.com

22 pointspassword432111 comments

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.

Fair question.

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.

https://en.wikipedia.org/wiki/Learning_to_rank#:~:text=Learn....

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>

A lot of things presented in this article reminded me of the same things that were in Joe Celko's “Trees and Hierarchies in SQL” book https://www.oreilly.com/library/view/joe-celkos-trees/978155...

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.

Not sure how practical this is.

    update todos set pos = pos+1 where pos >= 3 ORDER BY pos DESC

Am I missing something?

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.

[2] Author is using postgres so this would be the relevant doc https://www.postgresql.org/docs/current/sql-update.html

It works in MariaDB.

You are effectively updating many todos. The benefit of the solution presented by the author is not about performance, but rather minimizing change.

Imagine a situation when multiple clients keep copies of todos and rely on optimistic locking for updates.

This is a cool little problem brilliantly solved.

In that case, your update filters on the user_id.

In my real-life case, these were not todos, but cases reviewed by public agents.

These rotate between different agents and supervisors. The ownership is just an attribute intended for humans.

OTOH, rebalancing the order at night is not a problem.

Reply on news.ycombinator.com