SQL Server equivalent to Oracle's NULLS FIRST?

Solution 1:

You can do some trick:

ORDER BY (CASE WHEN [Order] IS NULL THEN 0 ELSE 1 END), [Order] 

Solution 2:

The quick answer is this: the best solution for changing the ordering of nulls in the necessary cases is the accepted one. But you only have to use it, or a variation of it in the necessary cases:

• DESC + NULLS FIRST:

  ORDER BY (CASE WHEN [Order] IS NULL THEN 0 ELSE 1 END), [Order] DESC

• ASC + NULLS LAST:

  ORDER BY (CASE WHEN [Order] IS NULL THEN 1 ELSE 0 END), [Order] ASC

• ASC + NULLS FIRST: it works fine by default

• DESC + NULLS LAST: it works fine by default

Let's see why:

If you check the ORDER BY Clause (Transact-SQL) MSDN docs, and scroll down to ASC | DESC, you can read this:

ASC | DESC

Specifies that the values in the specified column should be sorted in ascending or descending order. ASC sorts from the lowest value to highest value. DESC sorts from highest value to lowest value. ASC is the default sort order. Null values are treated as the lowest possible values.

So, by default if you specify ASC order, it works like NULLS FIRST. And, if you specify DESC, it works like NULLS LAST.

So you only need to do change the behavior for NULLS FIRST in DESC order, and for NULLS LAST in ASC order.

IMHO, the best solution for changing the ordering of nulls in the necessary cases is the accepted one, but I've included it adapted to the different cases in the beginning of my answer.