CODEWITHSUNDEEP
sqlpostgresqlsql-order-by
RandomB
RandomB

Reputation: 3747

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

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?

Upvotes: 4

Views: 1779

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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.

Upvotes: 5

Bill Karwin
Bill Karwin

Reputation: 562398

If it is stable in some case, that depends on the implementation. The implementor is not obligated to make the order of ties stable.

Even if it's stable in the current version of PostgreSQL for example, they would be free to change their code in the next release in some way that changes the default order, or makes it not stable from one invocation of the same query to the next invocation. They would not be violating any promise or standard by doing that.

So you should not rely on it being stable even from version to version of the same brand of RDBMS, nor between brands.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

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.

Upvotes: 5

Related Questions