Sarin Sarin - 13 days ago 6
SQL Question

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

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.

enter image description here

Please find the SQL Fiddle for this problem. SQL Fiddle

Answer

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