SQL Server equivalent to Oracle's NULLS FIRST?

Question

So Oracle has NULLS FIRST, which I can use to have null values sorted at the top followed by my column value in descending order:

ORDER BY date_sent NULLS FIRST

What is comparable in SQL Server? There are these alternatives, assuming the date values are NULL or in the past:

ORDER BY ISNULL(date_sent, GETDATE()) DESC
ORDER BY (CASE WHEN t.setinactive IS NULL THEN 1 ELSE 2 END), t.setinactive DESC
ORDER BY -CAST(date_sent as int) ASC

Any others?

Answer 1

You can't control this, to my knowledge. And it looks like you have the correct approach with ISNULL.

With strings, I've used ISNULL(field, '') for the same purpose.

Answer 2

Use Case/When statement, for example:

ORDER BY (case WHEN ColINT IS NULL THEN {maxIntValue} ELSE ColINT END) DESC

ORDER BY (case WHEN ColVChar IS NULL THEN {maxVCharValue} ELSE ColVChar END) DESC

ORDER BY (case WHEN ColDateT IS NULL THEN {maxDateTValue} ELSE ColDateT END) DESC

...and so on.

or even better as you don't care what is your column type and the max value.

ORDER BY (case WHEN ColAnyType IS NULL THEN 1 ELSE 0 END) DESC, ColAnyType DESC

Answer 3

You can do some trick:

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

Answer 4

If you have rows in your table with dates less than now, and other rows with dates greater than now, your NULLS would appear in the middle of the list. Instead, you should probably use a value that will never sort in the middle of your list.

Order by IsNull(Date_Sent, '17530101') desc

Note: That date is actually Jan 1, 1753.

Answer 5

ORDER BY
  COALESCE(POSTING_DATE,'1900-01-01 00:00:00.000')
 ,OTHER_FIELDS

Answer 6

This is an alternative way when you want to adjust how nulls appear in the sort order. Negate the column and reverse your sort order. Unfortunately you would need to CAST dateTime columns.

ORDER BY -CAST(date_sent as int) ASC