Hugo Kornelis (4/7/2016)
Stewart "Arturius" Campbell (4/6/2016)
Interesting, never thought of doing it that way
Nice question, thanks Horia
Nor should you. In production code, I would always write out the explicit intent instead of relying on such a trick.
ORDER BY CASE WHEN val IS NULL THEN 2 ELSE 1 END, -- Ensure that NULL sorts last
val -- Within non-NULL, order by ascending value
I'm not sure I'd call it a trick -- it's using a documented aspect of the SQL-92 standard (see my post on an early question at http://www.sqlservercentral.com/Forums/FindPost1768805.aspx
Having said that, it's not a commonly-known aspect, so at least commenting how it works would seem in order.
I'm not sure whether a CASE with a NULL test on every row would be more or less performant than a *-1 on every row, but that's something to consider as well.