SQL Server ORDER BY date and nulls last

Question

I am trying to order by date. I want the most recent dates coming in first. That's easy enough, but there are many records that are null and those come before any records that have a date.

I have tried a few things with no success:

ORDER BY ISNULL(Next_Contact_Date, 0)

ORDER BY ISNULL(Next_Contact_Date, 999999999)

ORDER BY coalesce(Next_Contact_Date, 99/99/9999)

How can I order by date and have the nulls come in last? The data type is smalldatetime.

Answer 1

If your SQL doesn't support NULLS FIRST or NULLS LAST, the simplest way to do this is to use the value IS NULL expression:

ORDER BY Next_Contact_Date IS NULL, Next_Contact_Date

to put the nulls at the end (NULLS LAST) or

ORDER BY Next_Contact_Date IS NOT NULL, Next_Contact_Date

to put the nulls at the front. This doesn't require knowing the type of the column and is easier to read than the CASE expression.

EDIT: Alas, while this works in other SQL implementations like PostgreSQL and MySQL, it doesn't work in MS SQL Server. I didn't have a SQL Server to test against and relied on Microsoft's documentation and testing with other SQL implementations. According to Microsoft, value IS NULL is an expression that should be usable just like any other expression. And ORDER BY is supposed to take expressions just like any other statement that takes an expression. But it doesn't actually work.

The best solution for SQL Server therefore appears to be the CASE expression.