Pregunta SQL selecciona solo las filas con el valor máximo en una columna


Tengo esta tabla para documentos (versión simplificada aquí):

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

¿Cómo selecciono una fila por identificación y solo la mayor rev?
Con los datos anteriores, el resultado debe contener dos filas: [1, 3, ...] y [2, 1, ..]. Estoy usando MySQL.

Actualmente uso cheques en el while loop para detectar y sobrescribir las viejas revoluciones del resultado. Pero, ¿es este el único método para lograr el resultado? ¿No hay un SQL ¿solución?

Actualizar
Como sugieren las respuestas, hay es una solución SQL, y aquí una demostración de sqlfiddle.

Actualización 2
Me di cuenta después de agregar lo anterior sqlfiddle, la velocidad a la que se sube la apuesta ha superado la velocidad de respuesta de las respuestas. ¡Esa no ha sido la intención! El violín se basa en las respuestas, especialmente la respuesta aceptada.


870
2017-10-12 19:42


origen


Respuestas:


A primera vista...

Todo lo que necesitas es un GROUP BY cláusula con el MAX función agregada:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

Nunca es tan simple, ¿verdad?

Me di cuenta de que necesitas el content columna también.

Esta es una pregunta muy común en SQL: encuentre toda la información de la fila con algún valor máximo en una columna por cada identificador de grupo. Lo escuché mucho durante mi carrera. En realidad, fue una de las preguntas que respondí en la entrevista técnica de mi trabajo actual.

En realidad, es tan común que la comunidad de StackOverflow haya creado una sola etiqueta solo para responder a preguntas como esa: .

Básicamente, tienes dos enfoques para resolver ese problema:

Unirse con simple group-identifier, max-value-in-group Subconsulta

En este enfoque, primero encuentras el group-identifier, max-value-in-group (ya resuelto arriba) en una sub consulta. Luego se une a su tabla a la sub consulta con igualdad en ambos group-identifier y max-value-in-group:

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

Izquierda Unirse con uno mismo, ajustar condiciones y filtros

En este enfoque, dejó unirse a la mesa consigo mismo. La igualdad, por supuesto, va en el group-identifier. Entonces, 2 movimientos inteligentes:

  1. La segunda condición de unión tiene un valor lateral izquierdo menor que el valor correcto
  2. Cuando hagas el paso 1, la (s) fila (s) que realmente tengan el valor máximo tendrán NULL en el lado derecho (es un LEFT JOIN, ¿recuerda?). Luego, filtramos el resultado unido, mostrando solo las filas donde está el lado derecho NULL.

Entonces terminas con:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

Conclusión

Ambos enfoques arrojan exactamente el mismo resultado.

Si tienes dos filas con max-value-in-group para group-identifier, ambas filas estarán en el resultado en ambos enfoques.

Ambos enfoques son compatibles con SQL ANSI, por lo tanto, funcionarán con su RDBMS favorito, independientemente de su "sabor".

Ambos enfoques también son amigables con el desempeño, sin embargo, su kilometraje puede variar (RDBMS, Estructura de DB, Índices, etc.). Entonces, cuando eliges un enfoque sobre el otro, punto de referencia. Y asegúrate de elegir el que tenga más sentido para ti.


1387
2017-10-12 19:43



Mi preferencia es usar el menor código posible ...

Puedes hacerlo usando IN prueba esto:

SELECT * 
FROM t1 WHERE (id,rev) IN 
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)

en mi opinión, es menos complicado ... más fácil de leer y mantener.


168
2017-10-12 19:47



Otra solución más es usar una subconsulta correlacionada:

select yt.id, yt.rev, yt.contents
    from YourTable yt
    where rev = 
        (select max(rev) from YourTable st where yt.id=st.id)

Tener un índice en (id, rev) hace que la subconsulta sea casi una simple búsqueda ...

Las siguientes son comparaciones con las soluciones en la respuesta de @ AdrianCarneiro (subconsulta, enlace a la izquierda), basadas en mediciones de MySQL con una tabla InnoDB de ~ 1 millón de registros, siendo el tamaño del grupo: 1-3.

Mientras que para los escaneos completos de tablas los tiempos de subconsulta / leftjoin / correlacionados se relacionan entre sí como 6/8/9, cuando se trata de búsquedas directas o por lotes (id in (1,2,3)), la subconsulta es mucho más lenta que las demás (debido a que se vuelve a ejecutar la subconsulta). Sin embargo, no pude diferenciar entre las soluciones correlacionadas de izquierda y las correlacionadas en velocidad.

Una nota final, ya que leftjoin crea n * (n + 1) / 2 uniones en grupos, su rendimiento puede verse muy afectado por el tamaño de los grupos ...


52
2018-01-23 14:16



No puedo responder por el rendimiento, pero aquí hay un truco inspirado en las limitaciones de Microsoft Excel. Tiene algunas buenas características

BUEN MATERIAL

  • Debería forzar el retorno de solo un "registro máximo" incluso si hay un empate (a veces útil)
  • No requiere una unión

ENFOQUE

Es un poco feo y requiere que sepa algo sobre el rango de valores válidos del Rdo columna. Supongamos que conocemos el Rdo la columna es un número entre 0.00 y 999 incluyendo decimales, pero que solo habrá dos dígitos a la derecha del punto decimal (por ejemplo, 34.17 sería un valor válido).

La esencia del asunto es que se crea una sola columna sintética mediante la concatenación / empaquetado del campo primario de comparación junto con los datos que se desean. De esta forma, puede obligar a la función de agregado MAX () de SQL a devolver todos los datos (porque se ha empaquetado en una sola columna). Luego debes descomprimir los datos.

Así es como se ve con el ejemplo anterior, escrito en SQL

SELECT id, 
       CAST(SUBSTRING(max(packed_col) FROM 2 FOR 6) AS float) as max_rev,
       SUBSTRING(max(packed_col) FROM 11) AS content_for_max_rev 
FROM  (SELECT id, 
       CAST(1000 + rev + .001 as CHAR) || '---' || CAST(content AS char) AS packed_col
       FROM yourtable
      ) 
GROUP BY id

El embalaje comienza forzando el Rdo columna es una cantidad de caracteres conocidos independientemente del valor de Rdo para que, por ejemplo

  • 3.2 se convierte en 1003.201
  • 57 se convierte en 1057.001
  • 923.88 pasa a ser 1923.881

Si lo haces bien, la comparación de dos números debe producir el mismo "máximo" que la comparación numérica de los dos números y es fácil convertir de nuevo al número original utilizando la función de subcadena (que está disponible de una forma u otra prácticamente en todos lados).


34
2018-06-30 06:02



Estoy sorprendido de que ninguna respuesta ofrecida solución de función de ventana SQL:

SELECT a.id, a.rev, a.contents
  FROM (SELECT id, rev, contents,
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
          FROM YourTable) a
 WHERE a.rank = 1 

Agregado en SQL estándar ANSI / ISO Estándar SQL: 2003 y posterior extendido con ANSI / ISO Estándar SQL: 2008, las funciones de ventana (o ventana) están disponibles ahora con todos los principales proveedores. Hay más tipos de funciones de rango disponibles para tratar un problema de empate: RANK, DENSE_RANK, PERSENT_RANK.


27
2017-08-09 15:29



Creo que esta es la solución más fácil:

SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
  • SELECT *: devuelve todos los campos.
  • FROM Empleado: Tabla buscada en.
  • (SELECCIONAR * ...) subconsulta: devolver a todas las personas, ordenadas por Salario.
  • GROUP BY employeesub.Salary:: Forzar la fila Salario superior ordenada de cada empleado para que sea el resultado devuelto.

Si necesita solo una fila, es aún más fácil:

SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1

También creo que es más fácil descomponer, comprender y modificar para otros fines:

  • ORDER BY Employee.Salary DESC: Ordene los resultados por salario, primero con los salarios más altos.
  • LÍMITE 1: devuelve solo un resultado.

Comprender este enfoque, resolver cualquiera de estos problemas similares se vuelve trivial: obtener un empleado con el salario más bajo (cambiar DESC a ASC), obtener empleados con ingresos entre los diez primeros (cambiar LIMIT 1 a LIMIT 10), ordenar por medio de otro campo (cambiar ORDER BY Employee.Salary a ORDER BY Employee.Commission), etc.


20
2017-09-14 00:28



¿Algo como esto?

SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
    SELECT id, max(rev) as maxrev FROM yourtable
    WHERE yourtable
    GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)

14
2017-10-12 19:48