6
Select      FullName, Login 
FROM        [User]
ORDER BY    FullName desc, login asc

Results

FullName                      Login
----------------------------- ------------------------------
M...... H.........            mh..@...com
A.... H.....                  ad..@...com
NULL                          and..@...com
NULL                          ben..@...com
NULL                          roc..@...com

What I want

FullName                      Login
----------------------------- ------------------------------
A.... H.....                  ad..@...com
M...... H.........            mh..@...com
NULL                          and..@...com
NULL                          ben..@...com
NULL                          roc..@...com

I want the full name in abc order desc and same with login, but I want all nulls to go to the bottom.

7
 Select      FullName, Login 
 FROM        [User]
 ORDER BY    CASE WHEN FullName IS NULL THEN 'ZZZZZZZ' ELSE FullName END, login asc
|improve this answer|||||
  • 3
    Your CASE could be shortened with COALESCE. – Martin Smith Jun 3 '16 at 22:30
  • 1
    @MartinSmith I agree. COALESCE or ISNULL would have been shorter. And I do like your answer better. It's better to pull the NULL piece out entirely. – Kenneth Fisher Jun 5 '16 at 16:31
  • Be careful of this pattern though: it can force extra index scans or a spool to tempdb. For small reaultsets the spool isn't an issue but it can be a significant performance issue otherwise. Even the UNION ALL version has the same problem though. As usual, benchmark against realistic data to make sure the performance is acceptable. – David Spillett Jun 6 '16 at 13:38
  • @DavidSpillett by preference I'm going to a) sort it at the application layer, b) sort the result set not the query itself (depending of course) – Kenneth Fisher Jun 6 '16 at 16:26
  • @DavidSpillett I didn't find that with the union all here stackoverflow.com/questions/5886857/… just gives a seek of the null and a seek of the not null and no sort or any tempdb usage is needed as long as there is a supporting index. – Martin Smith Jun 6 '16 at 17:04
27

There is a (closed) connect item requesting support for NULLS LAST in SQL Server.

A couple of other ways would be

ORDER BY 
        CASE WHEN FullName IS NULL THEN 1 ELSE 0 END, 
        FullName,
        Login

I prefer this as it doesn't rely on hardcoding a string that it is assumed no legitimate data will sort after. I'd rather not have to consider that on some case sensitive collations z sorts before Z and on others the reverse applies, will that be an issue? Similarly will "Željko Ivanek" sort before "ZZZZZZZ" reliably on all collations? Are there other characters from other languages that might exist in a name and sort after "Z"?

A potentially more efficient solution if there is an index on FullName, Login (as it hopefully will avoid a sort)

CREATE TABLE [User]
  (
     FullName VARCHAR(100),
     Login    VARCHAR(100),
     INDEX IX (FullName,Login) /*Use separate create index if < 2014*/
  );

WITH T
     AS (SELECT 1 AS Grp,
                *
         FROM   [User]
         WHERE  FullName IS NOT NULL
         UNION ALL
         SELECT 2 AS Grp,
                *
         FROM   [User]
         WHERE  FullName IS NULL)
SELECT FullName,
       Login
FROM   T
ORDER  BY Grp,
          FullName,
          Login 

enter image description here

|improve this answer|||||

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.