2

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?

3

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

  • thanks, but I do not understand how this CASE gives a priority and how it affects the performance? @RandiVertongen – igelr Mar 12 at 9:25
  • 1
    @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) – Randi Vertongen Mar 12 at 9:35
  • 1
    Thanks, now clear – igelr Mar 12 at 9:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.