mysql - una - ¿Cómo puedo SELECCIONAR filas con MAX(valor de columna), DISTINTO por otra columna en SQL?
seleccionar valores maximos sql (17)
¡Estás muy cerca! Todo lo que necesita hacer es seleccionar AMBOS el hogar y su hora máxima de fecha, y 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
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 distinta 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 tiene 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
No Da todos los récords.
-- 3 ..something exotic:
Con varios resultados.
¿Por qué no usar: SELECT home, MAX (datetime) AS MaxDateTime, player, resource FROM topten GROUP BY home ¿Perdí algo?
@Michae La respuesta aceptada funcionará bien en la mayoría de los casos, pero fallará en una como se muestra a continuación.
En caso de que si hubiera 2 filas con HomeID y Datetime iguales, la consulta devolverá ambas filas, no distinta a HomeID como se requiere, para esa adición Distinct 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
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 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)
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 hay función RANK () OVER en MySQL.
Pero se puede emular, vea Funciones de Emulación Analítica (Clasificación AKA) con MySQL .
Así que 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
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, solo haga una combinación con la tabla original (verifique Michael La Voie respuesta de Michael La Voie )
Atentamente.
Dado que la gente parece seguir corriendo en este hilo (la fecha de comentario varía de 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.
Esta es la consulta que necesitas:
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;
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
Esto funcionará incluso si tiene dos o más filas para cada home con DATETIME 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
Intente 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
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 :
Únete a la mesa consigo misma usando 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 la JOIN pide que coincida solo las filas de b que tienen un valor mayor en la columna datetime que la fila de m .
Usando los datos publicados en la pregunta, el 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 de arriba); 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 datetime y datetime columna.
Lea el libro Antipatterns de SQL: Cómo evitar los escollos de la programación de bases de datos para obtener otras sugerencias de SQL.
Otra forma de obtener la fila más reciente por grupo utilizando una sub consulta que básicamente calcula un rango para cada fila por grupo y luego filtra las filas más recientes como con rango = 1
select a.*
from topten a
where (
select count(*)
from topten b
where a.home = b.home
and a.`datetime` < b.`datetime`
) +1 = 1
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 ''inicio'' y ''fecha / hora''?
La consulta anterior fallará y devolverá más de 1 filas para la situación anterior. Para encubrir esta situación, será necesario otro criterio / parámetro / columna para decidir qué fila debe tomarse que cae en la situación anterior. Al ver el conjunto de datos de muestra, asumo que hay un id columna de clave principal que debe configurarse en incremento automático. Así que podemos usar esta columna para seleccionar la fila más reciente al ajustar la misma consulta con la ayuda de la declaración 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
La consulta anterior seleccionará la fila con el ID más alto 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
También puedes 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 arriba.
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
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
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)