CODEWITHSUNDEEP
sqlpostgresqlsql-order-by
Yuriy Matviyuk
Yuriy Matviyuk

Reputation: 101

PostgreSQL order by two columns with nullable last

I have the table:

id date bonus
1 2022-03-10 16:11:06.445559 6000
2 2022-03-15 16:11:06.445559 4000
3 2022-03-20 16:11:06.445559 null
4 2022-03-25 16:11:06.445559 7000
5 2022-03-30 16:11:06.445559 null

I need to order rows by date and bonus columns, using PostgreSQL syntax. First should be newest (order by date) rows with non-nullable bonuses. Then should be rows with nullable bonus ordered by date (also newest first) Result should be next:

id date bonus
4 2022-03-25 16:11:06.445559 7000
2 2022-03-15 16:11:06.445559 4000
1 2022-03-10 16:11:06.445559 6000
5 2022-03-30 16:11:06.445559 null
3 2022-03-20 16:11:06.445559 null

Upvotes: 2

Views: 1151

Answers (2)

Vesa Karjalainen
Vesa Karjalainen

Reputation: 1105

SELECT * 
FROM table
ORDER BY
    bonus IS NULL ASC,
    date DESC;

bonus IS NULL is a boolean and you can sort by it. In my opinion it's a bit cleaner (at least shorter) than CASE

Upvotes: 3

Barbaros Özhan
Barbaros Özhan

Reputation: 65433

You can use conditional for bonus while it has higher precedence, and then descendingly sorted date as the lower precedence such as

ORDER BY CASE WHEN bonus IS NULL THEN 1 ELSE 0 END, date DESC

Demo

Upvotes: 1

Related Questions