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 utilizo cheques en el ciclo while para detectar y sobreescribir viejas revoluciones del set de resultados. Pero, ¿es este el único método para lograr el resultado? ¿No hay una solución SQL ?

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

Actualización 2
Observé después de agregar el sqlfiddle anterior, la velocidad a la que se sube la votación 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.

A primera vista…

Todo lo que necesita es una cláusula GROUP BY con la function agregada MAX :

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

Nunca es tan simple, ¿verdad?

Me di count de que necesitas la columna de content 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.

Es, en realidad, tan común que la comunidad de StackOverflow haya creado una sola label solo para responder a preguntas como esa: greatest-n-per-group .

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

Unirse con un simple group-identifier, max-value-in-group sub-consulta group-identifier, max-value-in-group

En este enfoque, primero encontrará el group-identifier, max-value-in-group (ya resuelto anteriormente) en una subconsulta. Luego, une su tabla a la subconsulta con igualdad tanto group-identifier como 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 filters

En este enfoque, dejó join a la table 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 , ¿restrings?). Luego, filtramos el resultado unido, mostrando solo las filas donde el lado derecho es 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 tiene 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 reference . Y asegúrate de elegir el que tenga más sentido para ti.

Mi preference es usar el menor código posible …

Puedes hacerlo usando IN intente esto:

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

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

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 loggings, siendo el tamaño del grupo: 1-3.

Mientras que para los escaneos completos de tabla los times de subconsulta / 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 volver 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 performance puede verse muy afectado por el tamaño de los grupos …

No puedo responder por el performance, 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 "logging 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 range de valores válidos de la columna de rev . Supongamos que sabemos que la columna rev 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 function agregada 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 empaque comienza forzando a la columna de revolución a ser una cantidad de caracteres conocidos independientemente del valor de rev 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 numbers debe producir el mismo "máximo" que la comparación numérica de los dos numbers y es fácil convertir de nuevo al número original utilizando la function de subcadena (que está disponible de una forma u otra prácticamente en todos lados).

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, orderadas por Salario.
  • GROUP BY employeesub.Salary:: Forzar la fila Salario superior orderada 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.

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

Estoy sorprendido de que ninguna respuesta ofrecida solución de function de window 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 window (o window) están disponibles ahora con todos los principales proveedores. Hay más types de funciones de range disponibles para tratar un problema de empate: RANK, DENSE_RANK, PERSENT_RANK .

¿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) 

Como esta es la pregunta más popular con respecto a este problema, volveré a publicar otra respuesta aquí:

Parece que hay una forma más simple de hacer esto (pero solo en MySQL ):

 select * from (select * from mytable order by id, rev desc ) x group by id 

Acepte la respuesta del usuario Bohemian en esta pregunta para proporcionar una respuesta tan concisa y elegante a este problema.

EDITAR: aunque esta solución funciona para muchas personas, puede no ser estable a largo ploop, ya que MySQL no garantiza que la statement GROUP BY devuelva valores significativos para las columnas que no están en la list GROUP BY. Entonces use esta solución bajo su propio riesgo

Una tercera solución que casi nunca veo mencionada es específica de MySQL y se ve así:

 SELECT id, MAX(rev) AS rev , 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content FROM t1 GROUP BY id 

Sí, parece horrible (convertir a cadena y atrás, etc.) pero en mi experiencia, generalmente es más rápido que las otras soluciones. Tal vez sea solo para mis casos de uso, pero lo he usado en tablas con millones de loggings y muchos identificadores únicos. Tal vez sea porque MySQL es bastante malo para optimizar las otras soluciones (al less en los 5.0 días cuando se me ocurrió esta solución).

Una cosa importante es que GROUP_CONCAT tiene una longitud máxima para la cadena que puede build. Probablemente desee boost este límite configurando la variable group_concat_max_len . Y tenga en count que este será un límite en la escala si tiene una gran cantidad de filas.

De todos modos, lo anterior no funciona directamente si su campo de contenido ya es text. En ese caso, probablemente desee utilizar un separador diferente, como \ 0 tal vez. También se encontrará con el límite de group_concat_max_len más rápido.

Me gusta usar una solución basada en NOT EXIST para este problema:

 SELECT id, rev FROM YourTable t WHERE NOT EXISTS ( SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev ) 

Qué tal esto:

 select all_fields.* from (select id, MAX(rev) from yourtable group by id) as max_recs left outer join yourtable as all_fields on max_recs.id = all_fields.id 

Yo usaría esto:

 select t.* from test as t join (select max(rev) as rev from test group by id) as o on o.rev = t.rev 

La subconsulta SELECT no es muy eficiente tal vez, pero en la cláusula JOIN parece ser utilizable. No soy un experto en la optimization de consultas, pero lo he intentado en MySQL, PostgreSQL, FireBird y funciona muy bien.

Puede usar este esquema en varias combinaciones y con la cláusula WHERE. Es mi ejemplo de trabajo (la solución es idéntica a la tuya con la tabla "firmy"):

 select * from platnosci as p join firmy as f on p.id_rel_firmy = f.id_rel join (select max(id_obj) as id_obj from firmy group by id_rel) as o on o.id_obj = f.id_obj and p.od > '2014-03-01' 

Se le pide a las tablas que tienen miles de loggings de adolescentes, y lleva less de 0,01 segundos en la máquina realmente no demasiado fuerte.

No usaría la cláusula IN (como se menciona en alguna parte más arriba). IN se da para usar con lists cortas de constantes, y no como el filter de consultas creado en la subconsulta. Esto se debe a que se realiza una subconsulta en IN para cada logging escaneado, lo que puede hacer que la consulta lleve mucho time.

Si tiene muchos campos en la statement de selección y quiere el último valor para todos esos campos a través de un código optimizado:

 select * from (select * from table_name order by id,rev desc) temp group by id 

Muchas, sino todas, las otras respuestas aquí están bien para pequeños sets de datos. Para escalar, se necesita más cuidado. Mira aquí .

Discute múltiples forms más rápidas de hacer groupwise max y top-N por grupo.

Esta solución solo hace una selección de YourTable, por lo tanto, es más rápida. Funciona solo para MySQL y SQLite (para SQLite eliminar DESC) de acuerdo con la testing en sqlfiddle.com. Tal vez se pueda ajustar para trabajar en otros idiomas con los que no estoy familiarizado.

 SELECT * FROM ( SELECT * FROM ( SELECT 1 as id, 1 as rev, 'content1' as content UNION SELECT 2, 1, 'content2' UNION SELECT 1, 2, 'content3' UNION SELECT 1, 3, 'content4' ) as YourTable ORDER BY id, rev DESC ) as YourTable GROUP BY id 

NO mySQL , pero para otras personas que encuentran esta pregunta y usan SQL, otra forma de resolver el problema de mayor n-por-grupo es usar Cross Apply en MS SQL

 WITH DocIds AS (SELECT DISTINCT id FROM docs) SELECT d2.id, d2.rev, d2.content FROM DocIds d1 CROSS APPLY ( SELECT Top 1 * FROM docs d WHERE d.id = d1.id ORDER BY rev DESC ) d2 

Aquí hay un ejemplo en SqlFiddle

Here is a nice way of doing that

Use following code :

 with temp as ( select count(field1) as summ , field1 from table_name group by field1 ) select * from temp where summ = (select max(summ) from temp) 

I like to do this by ranking the records by some column. In this case, rank rev values grouped by id . Those with higher rev will have lower rankings. So highest rev will have ranking of 1.

 select id, rev, content from (select @rowNum := if(@prevValue = id, @rowNum+1, 1) as row_num, id, rev, content, @prevValue := id from (select id, rev, content from YOURTABLE order by id asc, rev desc) TEMP, (select @rowNum := 1 from DUAL) X, (select @prevValue := -1 from DUAL) Y) TEMP where row_num = 1; 

Not sure if introducing variables makes the whole thing slower. But at least I'm not querying YOURTABLE twice.

If anyone is looking for a Linq verson, this seems to work for me:

 public static IQueryable<BlockVersion> LatestVersionsPerBlock(this IQueryable<BlockVersion> blockVersions) { var max_version_per_id = blockVersions.GroupBy(v => v.BlockId) .Select( v => new { BlockId = v.Key, MaxVersion = v.Max(x => x.Version) } ); return blockVersions.Where( v => max_version_per_id.Any(x => x.BlockId == v.BlockId && x.MaxVersion == v.Version) ); } 

here is another solution hope it will help someone

 Select a.id , a.rev, a.content from Table1 a inner join (SELECT id, max(rev) rev FROM Table1 GROUP BY id) x on x.id =a.id and x.rev =a.rev 

None of these answers have worked for me.

This is what worked for me.

 with score as (select max(score_up) from history) select history.* from score, history where history.score_up = score.max 

SELECT * FROM Employee where Employee.Salary in (select max(salary) from Employee group by Employe_id) ORDER BY Employee.Salary

Here's another solution to retrieving the records only with a field that has the maximum value for that field. This works for SQL400 which is the platform I work on. In this example, the records with the maximum value in field FIELD5 will be retrieved by the following SQL statement.

 SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5 FROM MYFILE A WHERE RRN(A) IN (SELECT RRN(B) FROM MYFILE B WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2 ORDER BY B.FIELD5 DESC FETCH FIRST ROW ONLY) 

Sorted the rev field in reverse order and then grouped by id which gave the first row of each grouping which is the one with the highest rev value.

 SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id; 

Tested in http://sqlfiddle.com/ with the following data

 CREATE TABLE table1 (`id` int, `rev` int, `content` varchar(11)); INSERT INTO table1 (`id`, `rev`, `content`) VALUES (1, 1, 'One-One'), (1, 2, 'One-Two'), (2, 1, 'Two-One'), (2, 2, 'Two-Two'), (3, 2, 'Three-Two'), (3, 1, 'Three-One'), (3, 3, 'Three-Three') ; 

This gave the following result in MySql 5.5 and 5.6

 id rev content 1 2 One-Two 2 2 Two-Two 3 3 Three-Two 
 select * from yourtable group by id having rev=max(rev); 

This works for me in sqlite3:

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

With *, you get a duplicate rev column, but that's not much of a problem.

 SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;