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

Results

FullName                      Login
----------------------------- ------------------------------
M...... H.........            [email protected]
A.... H.....                  [email protected]
NULL                          [email protected]
NULL                          [email protected]
NULL                          [email protected]

What I want

FullName                      Login
----------------------------- ------------------------------
A.... H.....                  [email protected]
M...... H.........            [email protected]
NULL                          [email protected]
NULL                          [email protected]
NULL                          [email protected]

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

3 Answers 3

8
 Select      FullName, Login 
 FROM        [User]
 ORDER BY    CASE WHEN FullName IS NULL THEN 'ZZZZZZZ' ELSE FullName END, login asc
5
  • 4
    Your CASE could be shortened with COALESCE. Jun 3, 2016 at 22:30
  • 2
    @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. Jun 5, 2016 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. Jun 6, 2016 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) Jun 6, 2016 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. Jun 6, 2016 at 17:04
29

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

0
0

Being an old thread I assume it's sorted. I've tried all other methods, none work on Microsoft SQL Server, except this (only tested with column 'FullName').

Select  FullName, Login, ISNULL(FullName, 'ZZZ') AS SORTED
FROM   [User]
ORDER BY SORTED

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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