Conditional ORDER BY based on NULL values in the column

Question

I have a table like this. table(student_name nvarchar(20) not NULL, grades int). When I write

select * from table
order by grades asc

It shows this

student_name | grades
---------------------
 Ann | NULL
 Bob | NULL
 Bob | 10
 Jane | 25
 Nelly | 30

when select * from table order by grades desc

 student_name | grades
 ---------------------
 Nelly | 30
 Jane | 25
 Bob | 10
 Ann | NULL
 Bob | NULL

But I need to order by like this. NULLs in the end but those who have grades order by ascending order, like

 student_name | grades
 ---------------------
 Bob | 10
 Jane | 25
 Nelly | 30
 Ann | NULL
 Bob | NULL

How it is possible to do a conditional order by like this? How to specify NULL values to be at the end?

Answer 1

You could add a case when check to the order by to give the NULL's a lower priority and then filter on grades

SELECT * 
FROM table 
ORDER BY CASE WHEN grades IS NULL 
THEN 2 ELSE 1 END ASC, grades ASC;

Since you are using integers you can also do this, which should be more performant

SELECT * 
FROM table
ORDER BY -grades DESC;

Source

DB<>Fiddle

• igelr: thanks, but I do not understand how this CASE gives a priority and how it affects the performance? @RandiVertongen
• Randi Vertongen: @ElGrig no problem, it is simply saying if it is NULL, then put 2 else put 1, after this the second order comes into play, for all the 1's, order on grades asc. Afterwards order on the 2's the remaining grades (all NULL). I added a different possibly more performant approach (also from the source)