SQL Server ORDER BY date and nulls last

Question

I am trying to sort by date. I want the most recent dates to be first. It's easy enough, but there are many entries that are null, and they arrive before any entries that have a date.

I tried several things without 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 sort by date and have null values? The data type is smalldatetime .

Answer 1

smalldatetime has a range until June 6, 2079, so you can use

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

If no legal records have this date.

If this is not an assumption, you believe that you rely on a more reliable option, the sorting is done in two columns.

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

Both of the above suggestions cannot use an index to avoid sorting, and give similar plans.

Another 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-Gang, author of T-SQL basics for MS SQL Server 2012, "By default, SQL Server sorts NULL tags to NULL values. To get NULL tags to sort for the last time, you can use a CASE expression that returns 1 when Next_Contact_Date is NULL , "and 0 when it is not NULL . The <-> NULL characters are returned back from the expression, so they are sorted to NULL . > tags (which get 1). This CASE expression is used as the first sort column. "The Next_Contact_Date column must be specified as the second sort column. Thus, NULL labels are sorted correctly among themselves." Here is an example solution 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

A little late, but maybe someone will find it useful.

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

If you can change the design of the table, you can:

• Add another field, just for sorting, like Next_Contact_Date_Sort.

• Create a trigger that fills this 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 4

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

Answer 5

Standard ANSI Method:

 ORDER BY Next_Contact_Date ASC NULLS LAST 

Note that the opposite NULLS FIRST also exists.

Answer 6

If your SQL does not support NULLS FIRST or NULLS LAST , the easiest way to do this is to use the value IS NULL expression:

 ORDER BY Next_Contact_Date IS NULL, Next_Contact_Date 

to put zeros at the end ( NULLS LAST ) or

 ORDER BY Next_Contact_Date IS NOT NULL, Next_Contact_Date 

put zeros at the front. This does not require knowledge of the type of column and is easier to read than a CASE expression.

EDIT: Alas, while this works in other SQL implementations such as PostgreSQL and MySQL, it does not work in MS SQL Server. I did not have SQL Server for testing and using Microsoft documentation and testing with other SQL implementations. According to Microsoft, value IS NULL is an expression that should be used like any other expression. And ORDER BY must accept expressions just like any other statement that accepts an expression. But actually it does not work.

The best solution for SQL Server is a CASE expression.

Answer 7

Use desc and multiply by -1 if necessary. Example for an upstream int with a null value:

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