Can I be more specific with SQL ordering?

Question

I have the following SQL query:

SELECT  w.financial_year ,
        ISNULL(e.entity_name, 'Entity code ' + CAST(w.integer_1 AS VARCHAR) + ' is not available on the entity table. Please add.') ,
        COUNT(w.form_guid)
FROM    portal.workflow AS w
        LEFT OUTER JOIN property.entity AS e -- MS: Do not make inner join! Left outer join allows for exceptions to this case to be handled. Important as source data doesn't use property.entity
            ON w.integer_1 = e.entity_id
GROUP BY
        w.financial_year ,
        w.integer_1 ,
        e.entity_name
ORDER BY
        w.financial_year , e.entity_name

With my ordering I would like to show cases where e.entity_name was null first, and then sort the rest of the column alphabetically.. Is such a thing even possible?

http://stackoverflow.com/questions/1456653/sql-server-equivalent-to-oracles-nulls-first — Isaac, Nov 29 '12 at 04:34
http://stackoverflow.com/questions/821798/order-by-date-showing-nulls-first-then-most-recent-dates — Isaac, Nov 29 '12 at 04:35

score 3 · Accepted Answer · answered Nov 29 '12 at 04:36

of course,

  SELECT w.financial_year,
    ISNULL(e.entity_name, 'Entity code ' + CAST(w.integer_1 AS VARCHAR) + 
        ' is not available on the entity table. Please add.') ,
    COUNT(w.form_guid)
  FROM portal.workflow AS w
     LEFT JOIN property.entity AS e
         ON w.integer_1 = e.entity_id
  GROUP BY case When e.entity_name Is Null Then 0 Else 1 End,
        w.financial_year, w.integer_1, e.entity_name
  ORDER BY case When e.entity_name Is Null Then 0 Else 1 End, 
      w.financial_year, e.entity_name

score 2 · Answer 2 · answered Nov 29 '12 at 04:47

2

You can try this:

ORDER BY
        CASE WHEN e.entity_name IS NULL 
             THEN 0 
             ELSE w.financial_year END 
        ,e.entity_name

See this SQLFiddle

answered Nov 29 '12 at 04:47

Himanshu

31,810
31
111
133

score 0 · Answer 3 · answered Nov 29 '12 at 04:58

use NULLS FIRST or NULLS LAST in clause ORDER BY like this:

SELECT  w.financial_year ,
        ISNULL(e.entity_name, 'Entity code ' + CAST(w.integer_1 AS VARCHAR) + ' is not available on the entity table. Please add.') ,
        COUNT(w.form_guid)
FROM    portal.workflow AS w
        LEFT OUTER JOIN property.entity AS e -- MS: Do not make inner join! Left outer join allows for exceptions to this case to be handled. Important as source data doesn't use property.entity
            ON w.integer_1 = e.entity_id
GROUP BY
        w.financial_year ,
        w.integer_1 ,
        e.entity_name
ORDER BY
        w.financial_year , e.entity_name NULLS FIRST

oh sorry, it's for Oracle. see [SQL Server equivalent to Oracle's NULLS FIRST](http://stackoverflow.com/questions/1456653/sql-server-equivalent-to-oracles-nulls-first) — Andrey Khmelev, Nov 29 '12 at 05:03

Can I be more specific with SQL ordering?

3 Answers3

See this SQLFiddle