CODEWITHSUNDEEP
sqlpostgresqlsql-order-by
Davuz
Davuz

Reputation: 5286

How to change the default sort by some column in Postgres 10.2?

I'm getting a list of blog posts from Postgres

select id, post_priority, publish_start
from posts
where email = '[email protected]'
ORDER BY publish_start DESC

If there are many articles publishes the same date, the list is going wrong.

When i retrieved from Postgres 10.2 (server Ubuntu)

  id  | title |    post_priority     | publish_start
------+-------+----------------------+----------------
 1001 | A...  | 1                    | 2019-09-05
 1002 | B...  | 2                    | 2019-09-05

And it's different when I retrieved from my dev machine - Postgres 11 (MacOS) 

  id  | title |    post_priority     | publish_start
------+-------+----------------------+----------------
 1002 | B...  | 2                    | 2019-09-05
 1001 | A...  | 1                    | 2019-09-05

I know I just only need to add one more column to order in the query. But I still wonder what decided the default sort when publish_start dates are the same for all articles? How to change/config that default column?

Upvotes: 3

Views: 1257

Answers (1)

user330315
user330315

Reputation:

There is no such thing as a default sort order.

If two (or more) rows have the same value for the columns specified in the order by the ordering of those rose is undefined.

The only way to get a stable and guaranteed sort order is to add post_priority to the order by columns.

Upvotes: 7

Related Questions