Is ORDER BY stable for several rows with the same key values?

Question

Supposing I have a table friends like:

name   | age
-------+----
Xi     | 60
Joel   | 34
Kanna  | 10000
Gawr   | 10000

and I make something like SELECT * FROM friends ORDER BY age DESC LIMIT 1 and the result will be Kanna, 10000. But there is another candidate - Gawr. Is it correct to think that the result will be Kanna, 10000 absolutely always (so, the ordering is stable for rows with the same ordering key)? Is it true for all RDBMS? If - just for some of them, is it true at least for Postgres?

Answer 1

Your ORDER BY logic should always be specific enough to sort the way you want. So if you want Kanna's record to appear first in the limit query, you should add a descending sort level on the name:

SELECT * FROM friends ORDER BY age DESC, name DESC LIMIT 1;

Even if Postgres' sort appears to be stable with regard to a certain order, the behavior could change as the underlying data changes. And also the behavior almost certainly varies across different databases.

Answer 2

ORDER BY is not stable. Period.

Why not? SQL tables and result sets represent unordered sets (well, technically multi-sets because they can have duplicates).

Hence, there is no "default" ordering to fall back on. In fact, running the same ORDER BY on the same data could result in different orderings.

As an example, the sorting might not even use any sophisticated algorithm; it might use an index. The table could be reindexed -- and the ordering in the index could change, depending on the reindexing algorithm.

Sorting might "look" stable because it produces the same results. However, with no default ordering, you really cannot call it stable in any database. If you want a stable sort, include a unique key as the last key in the ORDER BY list.