sql - tutorial - Ordenar por valor mínimo de dos columnas

para que se usa elastic search

Question

Yo uso SQL Server 2008 R2 .

Necesito ordenar una tabla por el valor mínimo de dos columnas.

La tabla se ve así:

ID: integer; 
Date1: datetime; 
Date2: datetime.

Quiero que mis datos se ordenen por un mínimo de dos fechas.

¿Cuál es la forma más simple de ordenar esta tabla de esa manera?

Answer 1

Cambiaría el enfoque de cómo hacer esto a por qué lo necesita, y propondría cambiar el esquema en su lugar. La regla general es: si necesita realizar acrobacias para acceder a sus datos, hay una mala decisión de diseño.

Como ha visto, esta tarea es muy atípica para SQL, por lo que, aunque es posible, todos los métodos propuestos son extremadamente lentos en comparación con un ORDER BY ordinario.

• Si necesita hacer esto a menudo, entonces el mínimo de las dos fechas debe tener un significado físico independiente para su aplicación.
  • Lo que justifica una columna separada (o tal vez una columna que reemplaza a una de las dos), mantenida por un disparador o incluso manualmente si el significado es lo suficientemente independiente como para que la columna no lo sea en algunos casos.

Answer 2

Creo que cuando desea ordenar en ambos campos de date1 y date2 , debe tener ambos en la parte ORDER BY , así:

SELECT *
FROM aTable
ORDER BY 
    CASE WHEN date1 < date2 THEN date1 
    ELSE date2 END, 
    CASE WHEN date1 < date2 THEN date2 
    ELSE date1 END

El resultado puede ser así:

date1      | date2      
-----------+------------
2015-04-25 | 2015-04-21
2015-04-26 | 2015-04-21
2015-04-25 | 2015-04-22
2015-04-22 | 2015-04-26

Para tener un resultado Null con valores Null , use:

SELECT *
FROM aTable
ORDER BY 
    CASE 
        WHEN date1 IS NULL THEN NULL
        WHEN date1 < date2 THEN date1 
    ELSE date2 END 
    ,CASE 
        WHEN date2 IS NULL THEN date1
        WHEN date1 IS NULL THEN date2
        WHEN date1 < date2 THEN date2 
    ELSE date1 END

Los resultados serán así:

date1      | date2      
-----------+------------
NULL       | NULL
NULL       | 2015-04-22
2015-04-26 | NULL
2015-04-25 | 2015-04-21
2015-04-26 | 2015-04-21
2015-04-25 | 2015-04-22

Answer 3

Esta puede ser una solución alternativa que no requiere ramificación como CASE WHEN . Esto se basa en la fórmula max(a,b)=1/2(a+b+|a−b|) como se describe here . Obtenemos los valores absolutos de ayb usando DATEDIFF con una fecha de referencia ( ''1773-01-01'' ).

ORDER BY (DATEDIFF(d,''17730101'' ,isnull(Startdate,enddate)) + DATEDIFF(d,''17730101'' ,isnull(EndDate,Startdate)) 
    -  ABS(DATEDIFF(d,isnull(Startdate,enddate),isnull(EndDate,Startdate))))

Datos de prueba

Create Table #DateData(ID int Identity, Name varchar(15),Startdate datetime,EndDate DateTime)
Insert Into #DateData(Name,Startdate,EndDate) values (''myName'',''2015-04-17 18:48:27'',''2015-04-18 18:48:27'')
Insert Into #DateData(Name,Startdate,EndDate) values (''myName'',''2015-04-19 18:48:27'',''2015-04-18 18:48:27'')
Insert Into #DateData(Name,Startdate,EndDate) values (''myName'',''2015-04-20 18:48:27'',''2015-04-18 18:48:27'')
Insert Into #DateData(Name,Startdate,EndDate) values (''myName'',''2015-04-11 18:48:27'',''2015-04-22 18:48:27'')
Insert Into #DateData(Name,Startdate,EndDate) values (''myName'',''2015-05-09 18:48:27'',''2015-04-18 18:48:27'')
Insert Into #DateData(Name,Startdate,EndDate) values (''myName'',''2015-04-17 19:07:38'',''2015-04-17 18:55:38'')
Insert Into #DateData(Name,Startdate,EndDate) values (''myName'',''2015-04-17 19:07:38'',''2015-05-12 18:56:29'')

Consulta completa

select *
from #DateData order by (DATEDIFF(d,''17730101'' ,isnull(Startdate,enddate)) + DATEDIFF(d,''17730101'' ,isnull(EndDate,Startdate)) 
-  ABS(DATEDIFF(d,isnull(Startdate,enddate),isnull(EndDate,Startdate))))

Answer 4

Hay otra opción. Puede calcular la columna de resultados según la lógica necesaria y cubrir la selección por una externa con el pedido por su columna. En este caso el código será el siguiente:

select ID, x.Date1, x.Date2
from
(
    select
        ID,
        Date1,
        Date2, 
        SortColumn = case when Date1 < Date2 then Date1 else Date2 end
    from YourTable
) x
order by x.SortColumn

El beneficio de esta solución es que puede agregar las consultas de filtrado necesarias (en la selección interna) y aún así los índices serán útiles.

Answer 5

La forma más simple es usar la palabra clave VALUES , como la siguiente:

SELECT ID, Date1, Date2
FROM YourTable
ORDER BY (SELECT MIN(v) FROM (VALUES (Date1), (Date2)) AS value(v))

Este código funcionará para todos los casos, incluso con columnas anulables .

Editar:

La solución con la palabra clave COALESCE no es universal. Tiene las restricciones importantes:

• No funcionará si las columnas son del tipo Date (si usa las fechas anteriores al 01/01/1753 )
• No funcionará en caso de que una de las columnas sea NULL . Interpreta el valor NULL como el valor mínimo de datetime y datetime . Pero, ¿es realmente cierto? Ni siquiera es datetime y datetime , no es nada.
• La expresión IF será mucho más complicada si usamos más de dos columnas.

De acuerdo con la pregunta:

¿Cuál es la forma más simple de ordenar esta tabla de esa manera?

La solución más corta y más simple es la que se describió anteriormente, porque:

• No se necesita mucha codificación para implementarlo, simplemente agregue una línea más.
• No necesita preocuparse por si las columnas son anulables o no. Simplemente usa el código y funciona.
• Puede ampliar el número de columnas en su consulta simplemente agregando la que está después de una coma.
• Funciona con las columnas Date y no necesita modificar el código.

Edición 2:

Zohar Peled sugirió la siguiente forma de orden:

Ordenaría las filas según estas reglas: primero, cuando ambos son nulos, segundo, cuando date1 es nulo, tercero, cuando date 2 es nulo, cuarto, min (date1, date2)

Entonces, para este caso, se puede llegar a la solución utilizando el mismo enfoque, como el siguiente:

SELECT ID, Date1, Date2
FROM YourTable
ORDER BY 
CASE WHEN Date1 IS NULL AND Date2 IS NULL THEN 0
     WHEN Date1 IS NULL THEN 1
     WHEN Date2 IS NULL THEN 2
     ELSE 3 END,
(SELECT MIN(v) FROM (VALUES ([Date1]), ([Date2])) AS value(v))

La salida para este código está abajo:

La solución COALESCE no clasificará la tabla de esta manera. Desordena las filas donde al menos una celda del valor NULL . El resultado es el siguiente:

Espero que esto ayude y esperando críticas.

Answer 6

Ordenaría las filas según estas reglas:

1. cuando ambos son nulos
2. cuando date1 es nulo
3. cuando la fecha 2 es nula
4. min (fecha1, fecha2)

Para hacer esto, un caso anidado será simple y eficiente (a menos que la tabla sea muy grande) de acuerdo con esta publicación .

SELECT ID, Date1, Date2
FROM YourTable
ORDER BY 
CASE 
  WHEN Date1 IS NULL AND Date2 IS NULL THEN 0
  WHEN Date1 IS NULL THEN 1
  WHEN Date2 IS NULL THEN 2
  ELSE 3 END,
CASE 
  WHEN Date1 < Date2 THEN Date1
  ELSE Date2
END

Answer 7

Prefiero esta forma de manejar columnas anulables:

SELECT Id, Date1, Date2
FROM YourTable
ORDER BY 
   CASE 
     WHEN Date1 < Date2 OR Date1 IS NULL THEN Date1 
     ELSE Date2 
   END 

Answer 8

Puede usar la función min en order by cláusula:

select * 
from [table] d
order by ( select min(q.t) from (
           select d.date1 t union select d.date2) q
         )

También puede usar la declaración de case en order by cláusula, pero como sabe, el resultado de comparar ( > y < ) cualquier valor (nulo o nulo) con nulo no es true incluso si ha establecido ansi_nulls en off . por lo tanto, para garantizar el tipo que desea, debe manejar null , como sabe en la cláusula case si el resultado de a when es true luego when declaraciones no se evalúan, por lo que puede decir:

select * from [table]
order by case 
           when date1 is null then date2
           when date2 is null then date1 
           when date1<date2 then date1 -- surely date1 and date2 are not null here
           else date2 
         end

También aquí hay algunas otras soluciones si su escenario es diferente, tal vez evalúe el resultado de comparar múltiples columnas (o un cálculo) dentro de un campo separado y finalmente ordene por ese campo calculado sin usar ninguna condición dentro de su orden por cláusula.

Answer 9

Si no desea utilizar Case statement en Order By , entonces este es otro enfoque, simplemente moviendo la Case statement a Select

SELECT Id, Date1, Date2 FROM 
 (SELECT Id, Date1, Date2
  ,CASE WHEN Date1 < Date2 THEN Date1 ELSE Date2 END as MinDate 
FROM YourTable) as T
ORDER BY MinDate

Answer 10

Use una expresión CASE en ORDER BY :

 ORDER BY case when date1 < date2 then date1 else date2 end

Editar:

Si los valores nulos deben considerarse, agregue coalesce() :

 ORDER BY case when date1 < date2 then date1 else coalesce(date2,date1) end

Explicación:

Si date1 <date2 entonces ordene por date1. (Ambas fechas no son nulas aquí). Funciona igual que antes.

De lo contrario, use COALESCE() para ordenar por fecha2 (cuando date2 no es nulo), o date1 (cuando date2 es nulo), o por nulo (si ambas fechas son nulas).

Answer 11

Código para max

Estoy usando CROSS APPLY , no estoy seguro del rendimiento, pero CROSS APPLY menudo tiene un mejor rendimiento en mi experiencia.

CREATE TABLE #Test (ID INT, Date1 DATETIME, Date2 DATETIME)
INSERT INTO #Test SELECT 1, NULL, ''1/1/1'';INSERT INTO #Test SELECT 2, NULL, NULL;INSERT INTO #Test SELECT 3, ''2/2/2'', ''3/3/1'';INSERT INTO #Test SELECT 4, ''3/3/3'', ''11/1/1''

SELECT t.ID, Date1, Date2, MinDate
FROM #TEST t
    CROSS APPLY (SELECT MIN(d) MinDate FROM (VALUES (Date1), (Date2)) AS a(d)) md
ORDER BY MinDate

DROP TABLE #Test

Answer 12

NO columnas NULL . CASE agregar la declaración CASE en la cláusula ORDER BY de la siguiente manera:

SELECT Id, Date1, Date2
FROM YourTable
ORDER BY CASE 
           WHEN Date1 < Date2 THEN Date1 
           ELSE Date2 
         END 

Columnas anulables . Como Zohar Peled escribió en los comentarios si las columnas son anulables, podría usar ISNULL (pero es mejor usar COALESCE lugar de ISNULL , porque es el ANSI SQL standard ) de la siguiente manera:

SELECT Id, Date1, Date2
FROM YourTable
ORDER BY CASE 
           WHEN COALESCE(Date1, ''1753-01-01'') < COALESCE(Date2, ''1753-01-01'') THEN Date1 
           ELSE Date2 
         END

Puede leer sobre el 1753-01-01 estándar ANSI 1753-01-01 here .

Answer 13

SELECT ID, Date1, Date2
FROM YourTable
ORDER BY (SELECT TOP(1) v FROM (VALUES (Date1), (Date2)) AS value(v) ORDER BY v)

Muy similar a la respuesta @dyatchenko pero sin problema NULL