mysql - seleccionar - SQL selecciona solo filas con valor máximo en una columna

seleccionar valores maximos sql

Question

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 solo la mayor rev?
Con los datos anteriores, el resultado debe contener dos filas: [1, 3, ...] y [2, 1, ..] . Estoy usando MySQL .

Actualmente utilizo los controles en el bucle while para detectar y sobrescribir las revisiones antiguas del conjunto 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
Noté que después de agregar el sqlfiddle anterior, la tasa a la que se vota la pregunta ha superado la tasa de las respuestas. Esa no ha sido la intención! El violín se basa en las respuestas, especialmente la respuesta aceptada.

Answer 1

A primera vista...

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

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

Nunca es tan simple, ¿verdad?

Acabo de notar que también necesitas la columna de content .

Esta es una pregunta muy común en SQL: encuentre los datos completos de la fila con un valor máximo en una columna por algún identificador de grupo. Eso 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 ha creado una etiqueta única solo para responder a preguntas como esta: greatest-n-per-group .

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

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

En este enfoque, primero encontrará el group-identifier, max-value-in-group (ya resuelto arriba) 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

Uniéndose a la izquierda con uno mismo, ajustando las condiciones de unión y los filtros

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

1. La segunda condición de unión es tener un valor en el lado izquierdo menor que el valor correcto
2. Cuando realice el paso 1, las filas que realmente tienen el valor máximo tendrán NULL en el lado derecho (es una LEFT JOIN , ¿recuerda?). Luego, filtramos el resultado unido, mostrando solo las filas donde el lado derecho es NULL .

Así que 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 traen el mismo resultado exacto.

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

Ambos enfoques son compatibles con ANSI de SQL, por lo tanto, funcionarán con sus RDBMS favoritos, 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.). Así que cuando eliges un enfoque sobre el otro, referencia . Y asegúrese de elegir el que tenga más sentido para usted.

Answer 2

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

Answer 3

Aquí hay otra solución para recuperar los registros solo con un campo que tenga el valor máximo para ese campo. Esto funciona para SQL400, que es la plataforma en la que trabajo. En este ejemplo, los registros con el valor máximo en el campo FIELD5 se recuperarán mediante la siguiente instrucción SQL.

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)

Answer 4

Aquí hay otra solución, espero que ayude a alguien.

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

Answer 5

Aquí hay una buena manera de hacer eso.

Usa el siguiente código:

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)

Answer 6

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;

• SELECCIONAR *: Devuelve todos los campos.
• DE EMPLEADO: Tabla buscada.
• (SELECCIONAR * ...) subconsulta: Devolver a todas las personas, ordenadas por Salario.
• GRUPO POR empleadosub. Asalariado:: obligue a la fila de Salario mejor clasificada de cada empleado a ser el resultado devuelto.

Si necesitas 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 desglosar, comprender y modificar para otros propósitos:

• ORDER BY Employee.Salary DESC: Ordene los resultados por el salario, con los salarios más altos primero.
• 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 los diez empleados con mayores ingresos (cambiar de LÍMITE 1 a LÍMITE 10), ordenar por medio de otro campo (cambiar ORDER BY Employee.Salary to ORDER BY Employee.Commission), etc.

Answer 7

Esta solución hace solo 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 prueba en sqlfiddle.com. Tal vez se pueda modificar 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

Answer 8

Esto me funciona en sqlite3:

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

Con *, obtienes una columna de rev de duplicados, pero eso no es un gran problema.

Answer 9

Estoy asombrado de que no hay 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 el estándar SQL ANSI / ISO estándar SQL: 2003 y más tarde ampliado con el estándar ANSI / ISO SQL: 2008, las funciones de ventana (o ventanas) están disponibles con todos los principales proveedores ahora. Hay más tipos de funciones de rango disponibles para tratar un problema de empate: RANK, DENSE_RANK, PERSENT_RANK .

Answer 10

Me gusta hacer esto clasificando los registros por alguna columna. En este caso, clasifique los valores de rev agrupados por id . Aquellos con mayores rev tendrán una clasificación más baja. Así que la mayor rev tendrá un ranking de 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;

No estoy seguro si la introducción de variables hace que todo sea más lento. Pero al menos no estoy preguntando YOURTABLE dos veces.

Answer 11

Me gusta usar una solución 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
)

Answer 12

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.

Answer 13

Ninguna de estas respuestas me ha funcionado.

Esto es lo que funcionó para mí.

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

Answer 14

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

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

ENFOQUE

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

Lo esencial es que crea una sola columna sintética concatenando / empaquetando el campo de comparación principal junto con los datos que desea. De esta manera, puede forzar la función de agregado MAX () de SQL para devolver todos los datos (porque se ha empaquetado en una sola columna). Entonces tienes que 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 empaquetamiento comienza forzando a la columna de revoluciones a tener un número de caracteres conocidos independientemente del valor de rev, de modo que, por ejemplo,

• 3.2 se convierte en 1003.201
• 57 se convierte en 1057.001
• 923.88 se convierte en 1923.881

Si lo hace bien, la comparación de cadenas de dos números debería producir el mismo "máximo" que la comparación numérica de los dos números y es fácil volver a convertir al número original usando la función de subcadena (que está disponible de una forma u otra, de manera bastante más en todos lados).

Answer 15

Ordenó el campo de revoluciones en orden inverso y luego se agrupó por id, lo que dio la primera fila de cada agrupación, que es la que tiene el valor de rev más alto.

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

Probado en http://sqlfiddle.com/ con los siguientes datos

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'')
;

Esto dio el siguiente resultado en MySQL 5.5 y 5.6

id rev content
1 2 One-Two
2 2 Two-Two
3 3 Three-Two

Answer 16

Otra forma de hacer el trabajo es utilizando la función analítica MAX () en la cláusula OVER PARTITION

SELECT t.*
FROM
(
SELECT id
,rev
,contents
,MAX(rev) OVER (PARTITION BY id) as max_rev
FROM YourTable
) t
WHERE t.rev = t.max_rev

La otra solución de OVER PARTITION ya documentada en esta publicación es

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

Este 2 SELECT funciona bien en Oracle 10g.

Answer 17

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 ...

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

Mientras que para las exploraciones de tabla completa, los tiempos de subconsulta / combinación izquierda / correlacionada se relacionan entre sí como 6/8/9, cuando se trata de búsquedas directas o de lotes ( id in (1,2,3) ), la subconsulta es mucho más lenta que las otras ( Debido a la repetición de la subconsulta). Sin embargo, no pude diferenciar entre la combinación izquierda y las soluciones correlacionadas en 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 ...

Answer 18

Puede hacer la selección sin una combinación cuando combina rev y id en un valor maxRevId para MAX() y luego dividirlo en valores originales:

SELECT maxRevId & ((1 << 32) - 1) as id, maxRevId >> 32 AS rev
FROM (SELECT MAX(((rev << 32) | id)) AS maxRevId
FROM YourTable
GROUP BY id) x;

Esto es especialmente rápido cuando hay una combinación compleja en lugar de una sola tabla. Con los enfoques tradicionales, la combinación compleja se haría dos veces.

La combinación anterior es simple con las funciones de bit cuando rev y id están INT UNSIGNED (32 bit) y el valor combinado se ajusta a BIGINT UNSIGNED (64 bit). Cuando los valores de id y rev son mayores que los valores de 32 bits o están formados por varias columnas, necesita combinar el valor, por ejemplo, en un valor binario con un relleno adecuado para MAX() .

Answer 19

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

Answer 20

SELECT * FROM Employee donde Employee.Salary en (seleccione max (salario) de Employee_id por Employe_id) ORDEN POR Employee.Salary

Answer 21

Si alguien está buscando un Linq Verson, esto parece funcionar para mí:

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) );
}

Answer 22

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

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

Answer 23

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í, se ve horrible (se convierte en cadena y vuelve, etc.) pero, en mi experiencia, suele ser más rápido que las otras soluciones. Tal vez solo para mis casos de uso, pero lo he usado en tablas con millones de registros y muchos identificadores únicos. Tal vez sea porque MySQL es bastante malo en optimizar las otras soluciones (al menos en los 5.0 días en que se me ocurrió esta solución).

Una cosa importante es que GROUP_CONCAT tiene una longitud máxima para la cadena que puede construir. Probablemente quieras aumentar este límite configurando la variable group_concat_max_len . Y tenga en cuenta que esto será un límite en la escala si tiene un gran número de filas.

De todos modos, lo anterior no funciona directamente si su campo de contenido ya es texto. En ese caso, es probable que desee utilizar un separador diferente, como / 0 tal vez. También se ejecutará en el límite group_concat_max_len más rápido.

Answer 24

Utilicé lo siguiente para resolver un problema por mi cuenta. Primero creé una tabla temporal e inserté el valor máximo de revoluciones por ID única

CREATE TABLE #temp1
(
id varchar(20)
, rev int
)
INSERT INTO #temp1
SELECT a.id, MAX(a.rev) as rev
FROM
(
SELECT id, content, SUM(rev) as rev
FROM YourTable
GROUP BY id, content
) as a
GROUP BY a.id
ORDER BY a.id

Luego uní estos valores máximos (# temp1) a todas las posibles combinaciones de ID / contenido. Al hacer esto, naturalmente filtro las combinaciones de ID / contenido que no son máximas, y me quedan los únicos valores máximos de revoluciones para cada una.

SELECT a.id, a.rev, content
FROM #temp1 as a
LEFT JOIN
(
SELECT id, content, SUM(rev) as rev
FROM YourTable
GROUP BY id, content
) as b on a.id = b.id and a.rev = b.rev
GROUP BY a.id, a.rev, b.content
ORDER BY a.id

Answer 25

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

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

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

Por favor, conteste la respuesta del usuario Bohemian en esta pregunta por proporcionar una respuesta tan concisa y elegante a este problema.

EDIT: aunque esta solución funciona para muchas personas, puede que no sea estable a largo plazo, ya que MySQL no garantiza que la instrucción GROUP BY devuelva valores significativos para las columnas que no estén en la lista GROUP BY. Así que usa esta solución bajo tu propio riesgo

Answer 26

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

Tal vez la subconsulta SELECT no es demasiado eficiente, pero en la cláusula JOIN parece ser utilizable. No soy un experto en optimización de consultas, pero lo he probado 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 (resolviendo un problema idéntico al tuyo 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 pregunta en mesas con adolescentes y registros, y toma menos de 0,01 segundos en una máquina realmente no demasiado fuerte.

No usaría la cláusula IN (como se menciona en alguna parte arriba). Se da IN para usar con listas cortas de constantes, y no para ser el filtro de consulta construido en la subconsulta. Se debe a que la subconsulta en IN se realiza para cada registro escaneado, lo que puede hacer que las consultas tomen mucho tiempo.

Answer 27

NO es mySQL , pero para otras personas que encuentran esta pregunta y usan SQL, otra forma de resolver el problema greatest-n-per-group 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

Answer 28

SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;

Answer 29

select * from yourtable
group by id
having rev=max(rev);