¿Cómo puedo SELECCIONAR filas con MAX (valor de columna), PARTICIÓN por otra columna en MYSQL?
Tengo una tabla de rendimiento de los jugadores:
CREATE TABLE TopTen (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
home INT UNSIGNED NOT NULL,
`datetime`DATETIME NOT NULL,
player VARCHAR(6) NOT NULL,
resource INT NOT NULL
);
¿Qué consulta devolverá las filas para cada uno distinto homeque tenga su valor máximo de datetime? En otras palabras, ¿cómo puedo filtrar por el máximo datetime(agrupado por home) y aun así incluir otras columnas no agrupadas ni agregadas (como player) en el resultado?
Para estos datos de muestra:
INSERT INTO TopTen
(id, home, `datetime`, player, resource)
VALUES
(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)
;
el resultado debería ser:
| identificación | hogar | fecha y hora | jugador | recurso |
|---|---|---|---|---|
| 1 | 10 | 03/04/2009 | John | 399 |
| 2 | 11 | 03/04/2009 | julieta | 244 |
| 5 | 12 | 03/04/2009 | borat | 555 |
| 8 | 13 | 01/01/2009 | borat | 700 |
Probé una subconsulta obteniendo el máximo datetimepara cada una home:
-- 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
El conjunto de resultados tiene 130 filas, aunque la base de datos contiene 187, lo que indica que el resultado incluye algunos duplicados de home.
Luego intenté unirme a una subconsulta que obtiene el máximo datetimepara cada fila id:
-- 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`
No. Da todos los registros.
Probé varias consultas exóticas, cada una con diferentes resultados, pero nada que me acercara más a la solución de este problema.
¡Estás muy cerca! Todo lo que necesita hacer es seleccionar AMBOS la casa y su fecha y hora máxima, luego volver a unirse a la toptentabla 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
La MySQLsolución 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 :
Une la tabla consigo misma usando la homecolumna. El uso de LEFT JOINgarantiza que todas las filas de la tabla maparezcan en el conjunto de resultados. Aquellos que no tengan una coincidencia en la tabla btendrán NULLs para las columnas de b.
La otra condición solicita JOINhacer coincidir solo las filas bque tienen un valor mayor en la datetimecolumna que la fila de m.
Usando los datos publicados en la pregunta, LEFT JOINproducirán 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 WHEREcláusula mantiene sólo los pares que tienen NULLs en las columnas de b(están marcados con *en la tabla anterior); esto significa que, debido a la segunda condición de la JOINcláusula, la fila seleccionada mtiene el valor más grande en la columna datetime.
Lea el libro Antipatrones de SQL: cómo evitar los errores de la programación de bases de datos para obtener otros consejos sobre SQL.
Aquí va la versión 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 existe la función RANK() OVER en MySQL.
Pero se puede emular; consulte Emulación de funciones analíticas (también conocidas como clasificación) 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
Esto funcionará incluso si tiene dos o más filas para cada una homecon DATETIMEvalores iguales:
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 también necesita otras columnas, simplemente únalas con la tabla original (verifique Michael La Voiela respuesta)
Atentamente.
