Search code examples
sqlpostgresqlsql-order-by

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
Solution

  • 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