How do I order by NULLS LAST if my database doesn't support it? 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.
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 &lt; 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