SQL selecciona solo filas con valor máximo en una columna [duplicado]
Tengo esta tabla para documentos (versión simplificada aquí):
| identificación | Rdo | contenido |
|---|---|---|
| 1 | 1 | ... |
| 2 | 1 | ... |
| 1 | 2 | ... |
| 1 | 3 | ... |
¿Cómo selecciono una fila por identificación y solo la mayor revolución?
Con los datos anteriores, el resultado debería contener dos filas: [1, 3, ...]y [2, 1, ..]. Estoy usando MySQL .
Actualmente utilizo comprobaciones en el whilebucle para detectar y sobrescribir revoluciones antiguas del conjunto de resultados. ¿Pero es este el único método para lograr el resultado? ¿ No existe una solución SQL ?
A primera vista...
Todo lo que necesitas es una GROUP BYcláusula con la MAXfunción agregada:
SELECT id, MAX(rev)
FROM YourTable
GROUP BY id
Nunca es tan simple, ¿verdad?
contentMe acabo de dar cuenta de que también necesitas la columna.
Esta es una pregunta muy común en SQL: busque todos los datos de la fila con algún valor máximo en una columna por algún identificador de grupo. Escuché eso mucho durante mi carrera. De hecho, fue una de las preguntas que respondí en la entrevista técnica de mi trabajo actual.
De hecho, es tan común que la comunidad Stack Overflow ha creado una etiqueta única solo para abordar preguntas como esa:mayor n por grupo.
Básicamente, tienes dos enfoques para resolver ese problema:
Unirse con group-identifier, max-value-in-groupuna subconsulta simple
En este enfoque, primero encuentra group-identifier, max-value-in-group(ya resuelto anteriormente) en una subconsulta. Luego unes tu tabla a la subconsulta con igualdad en ambos group-identifiery 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 a uno mismo, ajustar las condiciones y filtros de unión
En este enfoque, dejaste unir la mesa consigo misma. La igualdad va en el group-identifier. Luego, 2 movimientos inteligentes:
- La segunda condición de unión es que el valor del lado izquierdo sea menor que el valor del derecho.
- Cuando realices el paso 1, las filas que realmente tienen el valor máximo estarán
NULLen el lado derecho (es unLEFT JOIN, ¿recuerdas?). Luego, filtramos el resultado unido, mostrando solo las filas donde está el lado derechoNULL.
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 producen exactamente el mismo resultado.
Si tiene dos filas con max-value-in-groupfor group-identifier, ambas filas estarán en el resultado en ambos enfoques.
Ambos enfoques son compatibles con SQL ANSI, por lo que funcionarán con su RDBMS favorito, independientemente de su "sabor".
Ambos enfoques también son amigables con el rendimiento, sin embargo, su kilometraje puede variar (RDBMS, estructura de base de datos, índices, etc.). Entonces, cuando elijas un enfoque sobre el otro, compara . Y asegúrese de elegir el que tenga más sentido para usted.
Mi preferencia es utilizar la menor cantidad de 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.
Estoy estupefacto de que ninguna respuesta ofreciera una 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) ranked_order
FROM YourTable) a
WHERE a.ranked_order = 1
Agregadas en el estándar SQL ANSI/ISO SQL:2003 y posteriormente ampliadas con el estándar ANSI/ISO SQL:2008, las funciones de ventana (o ventanas) están disponibles ahora con todos los principales proveedores. Hay más tipos de funciones de clasificación disponibles para solucionar un problema de empate: RANK, DENSE_RANK, PERSENT_RANK.
Otra solución más es utilizar 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) representa la subconsulta casi como una simple búsqueda...
A continuación se muestran comparaciones con las soluciones en la respuesta de @AdrianCarneiro (subconsulta, leftjoin), 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 de tablas completas los tiempos de subconsulta/unión izquierda/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 la combinación izquierda y las soluciones correlacionadas en cuanto a velocidad.
Una nota final, como leftjoin crea n*(n+1)/2 uniones en grupos, su rendimiento puede verse muy afectado por el tamaño de los grupos...
