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 id y sólo el mayor rev?
Con los datos anteriores, el resultado debería contener dos filas: [1, 3, ..]
y [2, 1, ..]
. Estoy utilizando MySQL.
Actualmente utilizo comprobaciones en el bucle while
para detectar y sobrescribir las revoluciones antiguas del conjunto de resultados. ¿Pero es este el único método para conseguir el resultado? ¿No hay una solución SQL?
Actualización Como sugieren las respuestas, existe una solución SQL, y aquí una demostración sqlfiddle.
Actualización 2 Me he dado cuenta de que después de añadir el sqlfiddle de arriba, la tasa de upvotes de la pregunta ha superado la tasa de upvotes de las respuestas. Esa no ha sido la intención. El fiddle se basa en las respuestas, especialmente en la respuesta aceptada.
Todo lo que necesitas es una cláusula GROUP BY
con la función agregada MAX
:
SELECT id, MAX(rev)
FROM YourTable
GROUP BY id
Acabo de darme cuenta de que también necesitas la columna content
.
Esta es una pregunta muy común en SQL: encontrar todos los datos de la fila con algún valor máximo en una columna por algún identificador de grupo. Lo he escuchado mucho durante mi carrera. De hecho, fue una de las preguntas que respondí en la entrevista técnica de mi trabajo actual.
Es, de hecho, tan común que la comunidad de StackOverflow ha creado una sola etiqueta sólo para tratar con preguntas como esa: [tag:greatest-n-per-group].
Básicamente, tienes dos enfoques para resolver ese problema:
identificador de grupo, valor máximo en el grupo
SubconsultaEn este enfoque, primero se encuentra el identificador de grupo, valor máximo en el grupo
(ya resuelto anteriormente) en una subconsulta. A continuación, se une la tabla a la subconsulta con igualdad en el "identificador de grupo" y el "valor máximo en el grupo":
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
En este enfoque, se une a la izquierda la tabla con sí misma. La igualdad va en el "identificador de grupo". Entonces, 2 movimientos inteligentes:
NULL
en el lado derecho (es un LEFT JOIN
, ¿recuerda?). Entonces, filtramos el resultado unido, mostrando sólo las filas donde el lado derecho es NULL
.Así que terminamos 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;
Ambos enfoques dan exactamente el mismo resultado.
Si tiene dos filas con valor máximo en el grupo
para identificador de grupo
, 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 son también amigables con el rendimiento, sin embargo, su kilometraje puede variar (RDBMS, estructura de la base de datos, índices, etc.). Así que cuando elijas un enfoque sobre el otro, compara el rendimiento. Y asegúrese de elegir el que tenga más sentido para usted.
Mi preferencia es utilizar el menor código posible...
Puedes hacerlo utilizando 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.
¿Algo así?
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)