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

smalldatetime has range up to June 6, 2079 so you can use

ORDER BY ISNULL(Next_Contact_Date, '2079-06-05T23:59:00')

If no legitimate records will have that date.

If this is not an assumption you fancy relying on a more robust option is sorting on two columns.

ORDER BY CASE WHEN Next_Contact_Date IS NULL THEN 1 ELSE 0 END, Next_Contact_Date

Both of the above suggestions are not able to use an index to avoid a sort however and give similar looking plans.

One other possibility if such an index exists is

SELECT 1 AS Grp, Next_Contact_Date 
FROM T 
WHERE Next_Contact_Date IS NOT NULL
UNION ALL
SELECT 2 AS Grp, Next_Contact_Date 
FROM T 
WHERE Next_Contact_Date IS NULL
ORDER BY Grp, Next_Contact_Date

Answer 2

According to Itzik Ben-Gan, author of T-SQL Fundamentals for MS SQL Server 2012, "By default, SQL Server sorts NULL marks before non-NULL values. To get NULL marks to sort last, you can use a CASE expression that returns 1 when the" Next_Contact_Date column is NULL, "and 0 when it is not NULL. Non-NULL marks get 0 back from the expression; therefore, they sort before NULL marks (which get 1). This CASE expression is used as the first sort column." The Next_Contact_Date column "should be specified as the second sort column. This way, non-NULL marks sort correctly among themselves." Here is the solution query for your example for MS SQL Server 2012 (and SQL Server 2014):

ORDER BY 
   CASE 
        WHEN Next_Contact_Date IS NULL THEN 1
        ELSE 0
   END, Next_Contact_Date;

Equivalent code using IIF syntax:

ORDER BY 
   IIF(Next_Contact_Date IS NULL, 1, 0),
   Next_Contact_Date;

Answer 3

order by -cast([Next_Contact_Date] as bigint) desc

Answer 4

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.

Answer 5

A bit late, but maybe someone finds it useful.

For me, ISNULL was out of question due to the table scan. UNION ALL would need me to repeat a complex query, and due to me selecting only the TOP X it would not have been very efficient.

If you are able to change the table design, you can:

1. Add another field, just for sorting, such as Next_Contact_Date_Sort.

2. Create a trigger that fills that field with a large (or small) value, depending on what you need:

   CREATE TRIGGER FILL_SORTABLE_DATE ON YOUR_TABLE AFTER INSERT,UPDATE AS 
   BEGIN
       SET NOCOUNT ON;
       IF (update(Next_Contact_Date)) BEGIN
       UPDATE YOUR_TABLE SET Next_Contact_Date_Sort=IIF(YOUR_TABLE.Next_Contact_Date IS NULL, 99/99/9999, YOUR_TABLE.Next_Contact_Date_Sort) FROM inserted i WHERE YOUR_TABLE.key1=i.key1 AND YOUR_TABLE.key2=i.key2
       END
   END
   

Answer 6

Use desc and multiply by -1 if necessary. Example for ascending int ordering with nulls last:

select * 
from
(select null v union all select 1 v union all select 2 v) t
order by -t.v desc

Answer 7

I know this is old but this is what worked for me

Order by Isnull(Date,'12/31/9999')

Answer 8

I think I found a way to show nulls in the end and still be able to use indexes for sorting.

The idea is super simple - create a calculatable column which will be based on existing column, and put an index on it.

ALTER TABLE dbo.Users 
ADD [FirstNameNullLast]  
AS (case when [FirstName] IS NOT NULL AND (ltrim(rtrim([FirstName]))<>N'' OR [FirstName] IS NULL) then [FirstName] else N'ZZZZZZZZZZ' end) PERSISTED

So, we are creating a persisted calculatable column in the SQL, in that column all blank and null values will be replaced by 'ZZZZZZZZ', this will mean, that if we will try to sort based on that column, we will see all the null or blank values in the end. Now we can use it in our new index.

Like this:

CREATE NONCLUSTERED INDEX [IX_Users_FirstNameNullLast] ON [dbo].[Users]
(
    [FirstNameNullLast] ASC
)

So, this is an ordinary nonclustered index. We can change it however we want, i.e. include extra columns, increase number of indexes columns, change sorting order etc.