PostgreSQL order by two columns with nullable last

Question

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

Vesa Karjalainen · Accepted Answer · 2022-03-23 14:29:40Z

3

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

edited Mar 23, 2022 at 14:29

answered Mar 23, 2022 at 11:42

Vesa Karjalainen

1,1058 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barbaros Özhan · Accepted Answer · 2022-03-23 12:11:26Z

1

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

answered Mar 23, 2022 at 12:11

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

3 Comments

Frank Heikens Over a year ago

There is no need for the CASE, just just a NULLS LAST: ORDER BY bonus DESC NULLS LAST, date DESC;

jjanes Over a year ago

@FrankHeikens That wouldn't treat all IS NOT NULL bonus as tied, which is what he seems to want.

Vesa Karjalainen Over a year ago

@FrankHeikens that would not give the result OP wanted. All non-null bonuses first, ordered by time (not ordered by bonus), after that null bonuses ordered by time.

Collectives™ on Stack Overflow

PostgreSQL order by two columns with nullable last

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related