CODEWITHSUNDEEP
sqlpostgresql
AntonTkachov
AntonTkachov

Reputation: 1784

ORDER BY discrepancies with/without ROW_NUMBER()

I am usign: PostgreSQL 9.6.3

Today I've faced a very strange discrepancies in selection result depending on whether ROW_NUMBER() function call present or not present in my query

The first query with output looks like:

SELECT "users"."id", "users"."ranking" FROM "users" WHERE "users"."mentor" = 't'  ORDER BY "users"."ranking" DESC LIMIT 10;

And it's result:

   id   |     ranking
--------+-----------------
     33 | 4.36852371411504
 320145 | 4.08071526845868
 301573 | 3.98071042776232
  25649 | 3.92019074023998
 159117 | 3.92019074023998
   3218 | 3.86067914389982
 635532 | 3.79788768116942
    559 | 3.73163558856403
 359211 | 3.73163558856403
 635548 | 3.66173214016947

The second query is very similar to the first, but I've added ROW_NUMBER() selection:

SELECT "users"."id", "users"."ranking", ROW_NUMBER() OVER (ORDER BY "users"."ranking" DESC) FROM "users" WHERE "users"."mentor" = 't' ORDER BY "users"."ranking" DESC LIMIT 10;

And it's result:

   id   |     ranking      | row_number 
--------+------------------+------------
     33 | 4.36852371411504 |          1
 320145 | 4.08071526845868 |          2
 301573 | 3.98071042776232 |          3
 159117 | 3.92019074023998 |          4
  25649 | 3.92019074023998 |          5
   3218 | 3.86067914389982 |          6
 635532 | 3.79788768116942 |          7
    559 | 3.73163558856403 |          8
 359211 | 3.73163558856403 |          9
 419302 | 3.66173214016947 |         10

As you see, it looks like in first case result is sorted by id in ASC order, while in second query it's get sorted by id in DESC order, but my queries are the same. I know HOW to solve this issue, but I want to understand WHY is it happen?

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You have multiple rows with the same value in the order by.

In relational databases, sorting is unstable. When there are ties in the key values, the rows are in an indeterminate order. This is what you are seeing.

The instability of SQL sorts is a direct result of the fact that relational tables (and result sets) represent unordered sets. There is no additional ordering. The solution is simple. For a stable sort, just add an additional unique key to the order by:

order by ranking desc, id

Upvotes: 3

Related Questions