Skip to content
Advertisement

How to order a query result by two columns with nulls last in one of the columns

I have the following table ‘client’

ID|name|filecount
---------------------- 
1  Jim  7
2  Jane null
3  Mike 18
4  Jeff 12
5  Ann  null
6  Helen 22

I want to fetch all rows from this table ordered by ID Desc, with nulls in the column filecount ordered last.

So after the sorting I would like to get the following order:

ID|name  |filecount 
---------------------- 
6  Helen 22
4  Jeff  12
3  Mike  18
1  Jim   7
5  Ann   null
2  Jane  null

I tried the following but it doesn’t work:

Select * from client order by id desc, (filecount > 0) nulls last

Advertisement

Answer

I would use a CASE expression here:

SELECT *
FROM client
ORDER BY
    CASE WHEN filecount IS NOT NULL THEN 0 ELSE 1 END,
    ID DESC;

screen capture from demo link below

Demo

5 People found this is helpful
Advertisement