¿Cómo puedo SELECCIONAR filas con MAX (valor de columna), DISTINCT por otra columna en SQL?

Mi mesa es:

id home datetime player resource ---|-----|------------|--------|--------- 1 | 10 | 04/03/2009 | john | 399 2 | 11 | 04/03/2009 | juliet | 244 5 | 12 | 04/03/2009 | borat | 555 3 | 10 | 03/03/2009 | john | 300 4 | 11 | 03/03/2009 | juliet | 200 6 | 12 | 03/03/2009 | borat | 500 7 | 13 | 24/12/2008 | borat | 600 8 | 13 | 01/01/2009 | borat | 700 

Necesito seleccionar cada home distinto que tenga el valor máximo de datetime .

El resultado sería:

 id home datetime player resource ---|-----|------------|--------|--------- 1 | 10 | 04/03/2009 | john | 399 2 | 11 | 04/03/2009 | juliet | 244 5 | 12 | 04/03/2009 | borat | 555 8 | 13 | 01/01/2009 | borat | 700 

Yo he tratado:

 -- 1 ..by the MySQL manual: SELECT DISTINCT home, id, datetime AS dt, player, resource FROM topten t1 WHERE datetime = (SELECT MAX(t2.datetime) FROM topten t2 GROUP BY home) GROUP BY datetime ORDER BY datetime DESC 

No funciona El conjunto de resultados tiene 130 filas, aunque la base de datos contiene 187. El resultado incluye algunos duplicados de home .

 -- 2 ..join SELECT s1.id, s1.home, s1.datetime, s1.player, s1.resource FROM topten s1 JOIN (SELECT id, MAX(datetime) AS dt FROM topten GROUP BY id) AS s2 ON s1.id = s2.id ORDER BY datetime 

Nop. Da todos los registros.

 -- 3 ..something exotic: 

Con varios resultados.

¡Estás muy cerca! Todo lo que necesita hacer es seleccionar AMBAS la casa y su fecha máxima, luego volver a unirse a la tabla topten en AMBOS campos:

 SELECT tt.* FROM topten tt INNER JOIN (SELECT home, MAX(datetime) AS MaxDateTime FROM topten GROUP BY home) groupedtt ON tt.home = groupedtt.home AND tt.datetime = groupedtt.MaxDateTime 

Aquí va la versión de T-SQL :

 -- Test data DECLARE @TestTable TABLE (id INT, home INT, date DATETIME, player VARCHAR(20), resource INT) INSERT INTO @TestTable SELECT 1, 10, '2009-03-04', 'john', 399 UNION SELECT 2, 11, '2009-03-04', 'juliet', 244 UNION SELECT 5, 12, '2009-03-04', 'borat', 555 UNION SELECT 3, 10, '2009-03-03', 'john', 300 UNION SELECT 4, 11, '2009-03-03', 'juliet', 200 UNION SELECT 6, 12, '2009-03-03', 'borat', 500 UNION SELECT 7, 13, '2008-12-24', 'borat', 600 UNION SELECT 8, 13, '2009-01-01', 'borat', 700 -- Answer SELECT id, home, date, player, resource FROM (SELECT id, home, date, player, resource, RANK() OVER (PARTITION BY home ORDER BY date DESC) N FROM @TestTable )M WHERE N = 1 -- and if you really want only home with max date SELECT T.id, T.home, T.date, T.player, T.resource FROM @TestTable T INNER JOIN ( SELECT TI.id, TI.home, TI.date, RANK() OVER (PARTITION BY TI.home ORDER BY TI.date) N FROM @TestTable TI WHERE TI.date IN (SELECT MAX(TM.date) FROM @TestTable TM) )TJ ON TJ.N = 1 AND T.id = TJ.id 

EDITAR
Desafortunadamente, no hay función RANK () OVER en MySQL.
Pero puede ser emulado, vea Emular funciones analíticas (Clasificación AKA) con MySQL .
Entonces esta es la versión de MySQL :

 SELECT id, home, date, player, resource FROM TestTable AS t1 WHERE (SELECT COUNT(*) FROM TestTable AS t2 WHERE t2.home = t1.home AND t2.date > t1.date ) = 0 

La solución MySQL más rápida, sin consultas internas y sin GROUP BY :

 SELECT m.* -- get the row that contains the max value FROM topten m -- "m" from "max" LEFT JOIN topten b -- "b" from "bigger" ON m.home = b.home -- match "max" row with "bigger" row by `home` AND m.datetime < b.datetime -- want "bigger" than "max" WHERE b.datetime IS NULL -- keep only if there is no bigger than max 

Explicación

Únase a la mesa consigo mismo utilizando la columna de home . El uso de LEFT JOIN garantiza que todas las filas de la tabla m aparezcan en el conjunto de resultados. Aquellos que no tienen una coincidencia en la tabla b tendrán NULL s para las columnas de b .

La otra condición en JOIN pide que coincida solo las filas de b que tienen un valor mayor en la columna de datetime que la fila de m .

Usando los datos publicados en la pregunta, LEFT JOIN producirá estos pares:

 +------------------------------------------+--------------------------------+ | the row from `m` | the matching row from `b` | |------------------------------------------|--------------------------------| | id home datetime player resource | id home datetime ... | |----|-----|------------|--------|---------|------|------|------------|-----| | 1 | 10 | 04/03/2009 | john | 399 | NULL | NULL | NULL | ... | * | 2 | 11 | 04/03/2009 | juliet | 244 | NULL | NULL | NULL | ... | * | 5 | 12 | 04/03/2009 | borat | 555 | NULL | NULL | NULL | ... | * | 3 | 10 | 03/03/2009 | john | 300 | 1 | 10 | 04/03/2009 | ... | | 4 | 11 | 03/03/2009 | juliet | 200 | 2 | 11 | 04/03/2009 | ... | | 6 | 12 | 03/03/2009 | borat | 500 | 5 | 12 | 04/03/2009 | ... | | 7 | 13 | 24/12/2008 | borat | 600 | 8 | 13 | 01/01/2009 | ... | | 8 | 13 | 01/01/2009 | borat | 700 | NULL | NULL | NULL | ... | * +------------------------------------------+--------------------------------+ 

Finalmente, la cláusula WHERE mantiene solo los pares que tienen NULL s en las columnas de b (están marcados con * en la tabla anterior); esto significa que, debido a la segunda condición de la cláusula JOIN , la fila seleccionada de m tiene el mayor valor en la columna datetime .

Lea los Antipatterns de SQL: Cómo evitar las trampas del libro de Progtwigción de Base de Datos para otras sugerencias de SQL.

Esto funcionará incluso si tiene dos o más filas para cada home con igual DATETIME :

 SELECT id, home, datetime, player, resource FROM ( SELECT ( SELECT id FROM topten ti WHERE ti.home = t1.home ORDER BY ti.datetime DESC LIMIT 1 ) lid FROM ( SELECT DISTINCT home FROM topten ) t1 ) ro, topten t2 WHERE t2.id = ro.lid 

Creo que esto te dará el resultado deseado:

 SELECT home, MAX(datetime) FROM my_table GROUP BY home 

PERO si necesita otras columnas también, solo haga una combinación con la tabla original (consulte Michael La Voie respuesta de Michael La Voie )

Atentamente.

Dado que las personas parecen seguir corriendo en este hilo (la fecha del comentario oscila entre 1,5 años) no es mucho más simple:

SELECT * FROM (SELECT * FROM topten ORDER BY datetime DESC) tmp GROUP BY home

No se necesitan funciones de agregación …

Aclamaciones.

También puede probar este y para tablas grandes el rendimiento de las consultas será mejor. Funciona cuando no hay más de dos registros para cada hogar y sus fechas son diferentes. Mejor consulta general de MySQL es una de Michael La Voie anterior.

 SELECT t1.id, t1.home, t1.date, t1.player, t1.resource FROM t_scores_1 t1 INNER JOIN t_scores_1 t2 ON t1.home = t2.home WHERE t1.date > t2.date 

O en el caso de Postgres o aquellos dbs que proporcionan funciones analíticas, intente

 SELECT t.* FROM (SELECT t1.id, t1.home, t1.date, t1.player, t1.resource , row_number() over (partition by t1.home order by t1.date desc) rw FROM topten t1 INNER JOIN topten t2 ON t1.home = t2.home WHERE t1.date > t2.date ) t WHERE t.rw = 1 

Esto funciona en Oracle:

 with table_max as( select id , home , datetime , player , resource , max(home) over (partition by home) maxhome from table ) select id , home , datetime , player , resource from table_max where home = maxhome 
 SELECT tt.* FROM TestTable tt INNER JOIN ( SELECT coord, MAX(datetime) AS MaxDateTime FROM rapsa GROUP BY krd ) groupedtt ON tt.coord = groupedtt.coord AND tt.datetime = groupedtt.MaxDateTime 

Pruebe esto para SQL Server:

 WITH cte AS ( SELECT home, MAX(year) AS year FROM Table1 GROUP BY home ) SELECT * FROM Table1 a INNER JOIN cte ON a.home = cte.home AND a.year = cte.year 
 SELECT c1, c2, c3, c4, c5 FROM table1 WHERE c3 = (select max(c3) from table) SELECT * FROM table1 WHERE c3 = (select max(c3) from table1) 

Otra forma de obtener la fila más reciente por grupo usando una sub consulta que básicamente calcula un rango para cada fila por grupo y luego filtra las filas más recientes como con rank = 1

 select a.* from topten a where ( select count(*) from topten b where a.home = b.home and a.`datetime` < b.`datetime` ) +1 = 1 

MANIFESTACIÓN

Aquí está la demostración visual para el rango no para cada fila para una mejor comprensión

Al leer algunos comentarios, ¿qué pasa si hay dos filas que tienen los mismos valores de campo "de inicio" y "de fecha y hora"?

La consulta anterior fallará y devolverá más de 1 fila para la situación anterior. Para encubrir esta situación, habrá una necesidad de otro criterio / parámetro / columna para decidir qué fila se debe tomar que cae en la situación anterior. Al ver el conjunto de datos de muestra supongo que hay una id columna de clave principal que se debe establecer en auto incremento. Así que podemos usar esta columna para elegir la fila más reciente ajustando la misma consulta con la ayuda de la sentencia CASE , como

 select a.* from topten a where ( select count(*) from topten b where a.home = b.home and case when a.`datetime` = b.`datetime` then a.id < b.id else a.`datetime` < b.`datetime` end ) + 1 = 1 

MANIFESTACIÓN

La consulta anterior seleccionará la fila con la identificación más alta entre los mismos valores de datetime

demostración visual para el rango no para cada fila

Prueba esto

 select * from mytable a join (select home, max(datetime) datetime from mytable group by home) b on a.home = b.home and a.datetime = b.datetime 

Saludos K

Aquí está la versión de MySQL que imprime solo una entrada donde hay duplicados MAX (datetime) en un grupo.

Puede probar aquí http://www.sqlfiddle.com/#!2/0a4ae/1

Data de muestra

 mysql> SELECT * from topten; +------+------+---------------------+--------+----------+ | id | home | datetime | player | resource | +------+------+---------------------+--------+----------+ | 1 | 10 | 2009-04-03 00:00:00 | john | 399 | | 2 | 11 | 2009-04-03 00:00:00 | juliet | 244 | | 3 | 10 | 2009-03-03 00:00:00 | john | 300 | | 4 | 11 | 2009-03-03 00:00:00 | juliet | 200 | | 5 | 12 | 2009-04-03 00:00:00 | borat | 555 | | 6 | 12 | 2009-03-03 00:00:00 | borat | 500 | | 7 | 13 | 2008-12-24 00:00:00 | borat | 600 | | 8 | 13 | 2009-01-01 00:00:00 | borat | 700 | | 9 | 10 | 2009-04-03 00:00:00 | borat | 700 | | 10 | 11 | 2009-04-03 00:00:00 | borat | 700 | | 12 | 12 | 2009-04-03 00:00:00 | borat | 700 | +------+------+---------------------+--------+----------+ 

Versión de MySQL con variable de usuario

 SELECT * FROM ( SELECT ord.*, IF (@prev_home = ord.home, 0, 1) AS is_first_appear, @prev_home := ord.home FROM ( SELECT t1.id, t1.home, t1.player, t1.resource FROM topten t1 INNER JOIN ( SELECT home, MAX(datetime) AS mx_dt FROM topten GROUP BY home ) x ON t1.home = x.home AND t1.datetime = x.mx_dt ORDER BY home ) ord, (SELECT @prev_home := 0, @seq := 0) init ) y WHERE is_first_appear = 1; +------+------+--------+----------+-----------------+------------------------+ | id | home | player | resource | is_first_appear | @prev_home := ord.home | +------+------+--------+----------+-----------------+------------------------+ | 9 | 10 | borat | 700 | 1 | 10 | | 10 | 11 | borat | 700 | 1 | 11 | | 12 | 12 | borat | 700 | 1 | 12 | | 8 | 13 | borat | 700 | 1 | 13 | +------+------+--------+----------+-----------------+------------------------+ 4 rows in set (0.00 sec) 

Respuestas aceptadas

 SELECT tt.* FROM topten tt INNER JOIN ( SELECT home, MAX(datetime) AS MaxDateTime FROM topten GROUP BY home ) groupedtt ON tt.home = groupedtt.home AND tt.datetime = groupedtt.MaxDateTime +------+------+---------------------+--------+----------+ | id | home | datetime | player | resource | +------+------+---------------------+--------+----------+ | 1 | 10 | 2009-04-03 00:00:00 | john | 399 | | 2 | 11 | 2009-04-03 00:00:00 | juliet | 244 | | 5 | 12 | 2009-04-03 00:00:00 | borat | 555 | | 8 | 13 | 2009-01-01 00:00:00 | borat | 700 | | 9 | 10 | 2009-04-03 00:00:00 | borat | 700 | | 10 | 11 | 2009-04-03 00:00:00 | borat | 700 | | 12 | 12 | 2009-04-03 00:00:00 | borat | 700 | +------+------+---------------------+--------+----------+ 7 rows in set (0.00 sec) 

¿Por qué no usar: SELECT home, MAX (datetime) AS MaxDateTime, player, resource FROM topten GROUP BY home ¿Extrañé algo?

esta es la consulta que necesita:

  SELECT b.id, a.home,b.[datetime],b.player,a.resource FROM (SELECT home,MAX(resource) AS resource FROM tbl_1 GROUP BY home) AS a LEFT JOIN (SELECT id,home,[datetime],player,resource FROM tbl_1) AS b ON a.resource = b.resource WHERE a.home =b.home; 

@Michae La respuesta aceptada funcionará bien en la mayoría de los casos pero falla para uno de los siguientes.

En caso de que hubiera 2 filas con ID de inicio y Fecha y hora iguales, la consulta devolverá ambas filas, no el ID de inicio distinto, según sea necesario, para las que se agreguen Distinto en la consulta, como se muestra a continuación.

 SELECT DISTINCT tt.home , tt.MaxDateTime FROM topten tt INNER JOIN (SELECT home, MAX(datetime) AS MaxDateTime FROM topten GROUP BY home) groupedtt ON tt.home = groupedtt.home AND tt.datetime = groupedtt.MaxDateTime