SQL Server ORDER BY date y nulls last

Estoy tratando de ordenar por fecha. Quiero que lleguen las fechas más recientes primero. Eso es bastante fácil, pero hay muchos registros que son nulos y vienen antes de cualquier registro que tenga una fecha.

He intentado algunas cosas sin éxito:

ORDER BY ISNULL(Next_Contact_Date, 0) ORDER BY ISNULL(Next_Contact_Date, 999999999) ORDER BY coalesce(Next_Contact_Date, 99/99/9999) 

¿Cómo puedo ordenar por fecha y hacer que los nulos sean los últimos? El tipo de datos es smalldatetime .

smalldatetime tiene rango hasta el 6 de junio de 2079 para que pueda usar

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

Si no hay registros legítimos tendrá esa fecha.

Si no se trata de una suposición, puede confiar en una opción más sólida para clasificar dos columnas.

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

Ambas sugerencias anteriores no pueden usar un índice para evitar una clasificación y ofrecen planes similares.

enter image description here

Otra posibilidad si existe tal índice es

 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 

Plan

De acuerdo con Itzik Ben-Gan, autor de Fundamentos de T-SQL para MS SQL Server 2012 , “de forma predeterminada, SQL Server clasifica las marcas NULL antes de los valores no nulos . Para obtener las marcas NULL para ordenar por último, puede utilizar una expresión CASE que devuelve 1 cuando la columna ” Next_Contact_Date es NULL ” y 0 cuando no es NULL . Las marcas que no son NULL obtienen 0 de la expresión, por lo tanto, clasifican antes de las marcas NULL (que obtienen 1). Esta expresión CASE se usa como la primera ordenar la columna “. La columna Next_Contact_Date “debe especificarse como la segunda columna de clasificación. De esta forma, las marcas que no son NULL se ordenan correctamente entre ellos”. Aquí está la consulta de solución para su ejemplo para MS SQL Server 2012 (y SQL Server 2014):

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

Código equivalente usando syntax IIF:

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

Un poco tarde, pero tal vez alguien lo encuentre útil.

Para mí, ISNULL estaba fuera de dudas debido a la exploración de la tabla. UNION ALL me necesitaría para repetir una consulta compleja, y debido a que seleccioné solo TOP X, no habría sido muy eficiente.

Si puede cambiar el diseño de la mesa, puede:

  1. Agregue otro campo, solo para ordenar, como Next_Contact_Date_Sort.

  2. Cree un disparador que llene ese campo con un valor grande (o pequeño), según lo que necesite:

     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 
 order by -cast([Next_Contact_Date] as bigint) desc 

Si su SQL no es compatible con NULLS FIRST o NULLS LAST , la forma más sencilla de hacerlo es utilizar la expresión de value IS NULL :

 ORDER BY Next_Contact_Date IS NULL, Next_Contact_Date 

poner los nulos al final ( NULLS LAST ) o

 ORDER BY Next_Contact_Date IS NOT NULL, Next_Contact_Date 

para poner los nulos en el frente. Esto no requiere conocer el tipo de columna y es más fácil de leer que la expresión CASE .

EDITAR: Por desgracia, mientras esto funciona en otras implementaciones de SQL como PostgreSQL y MySQL, no funciona en MS SQL Server. No tenía un servidor SQL para probar y confié en la documentación y las pruebas de Microsoft con otras implementaciones de SQL. Según Microsoft, el value IS NULL es una expresión que debería ser utilizable como cualquier otra expresión. Y se supone que ORDER BY toma expresiones como cualquier otra statement que tome una expresión. Pero en realidad no funciona.

La mejor solución para SQL Server parece ser la expresión CASE .

Use desc y multiplique por -1 si es necesario. Ejemplo para ordenamiento int ascendente con nulos por última vez:

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