NULL values appear before empty strings when doing an ORDER BY in SQL

Question

I was trying to select some data from a table Local_LsIr_Temp. I need the data in such a way that, it should be ordered by:

1. Both dir_email and dir_tele are present.
2. At least dir_email is present (dir_tele can be null or empty)
3. At least dir_tele is present (dir_email can be null or empty)

SELECT ROW_NUMBER() OVER 
(PARTITION BY inst_iconum ORDER BY 
    CASE WHEN (dir_email = '' OR dir_email IS NULL) THEN 1 ELSE 0 END, dir_email,
    CASE WHEN (dir_tele = ''  OR dir_tele  IS NULL) THEN 1 ELSE 0 END, dir_tele
) rn,
* 
FROM Local_LsIr_Temp

I wrote the above query and the result is not coming as expected.

Attached below is sample result, the row marked in blue, should come above the row with both null values.

Please find the SQL Fiddle for this problem. SQL Fiddle

Answer 1

The CASE statements must be specified first. Having said that, you can achieve desired results by rewriting the ORDER BY clause:

ORDER BY CASE 
    WHEN dir_email <> '' AND dir_tele <> '' THEN 1 -- Both dir_email and dir_tele are present
    WHEN dir_email <> ''                    THEN 2 -- At least dir_email is present
    WHEN dir_tele  <> ''                    THEN 3 -- At least dir_tele is present 
    ELSE                                         4
END --, additional columns here

Answer 2

Your problem is you have the order columns in the wrong order - if dir_email is non-blank, dir_tele is basically ignored.

Try this:

...
order by 
  case when (dir_email = '' or dir_email is null) then 1 else 0 end,
  case when (dir_tele = '' or dir_tele is null) then 1 else 0 end,
  dir_enail,
  dir_tele