Comment puis-je sélectionner des lignes avec MAX(valeur de la colonne), distinctes par une autre colonne en SQL?

ma table est:

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

je dois sélectionner chaque distinct home contenant la valeur maximale de datetime .

résultat:

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

j'ai essayé:

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

ne marche pas. Résultat-ensemble a 130 lignes bien que la base de données détient 187. Le résultat inclut des doublons 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 

Non. Donne tous les enregistrements.

-- 3 ..something exotic: 

avec divers résultats.

655
demandé sur DineshDB 2009-03-04 23:14:26

17 réponses

vous êtes si près! Tout ce que vous devez faire est de sélectionner à la fois la maison et son heure de date max, puis de rejoindre de nouveau à la topten table sur les deux champs:

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
801
répondu Michael La Voie 2018-02-14 12:06:06

Here goes T-SQL version:

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

MODIFIER

Malheureusement, il n'y a pas de rang() sur la fonction dans MySQL.

Mais il peut être émulé, voir émulation fonctions analytiques (aka classement) avec MySQL .

Donc c'est MySQL version:

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
69
répondu Maksym Gontar 2009-03-06 17:59:59

la solution la plus rapide MySQL , sans requêtes internes et sans 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

explication :

rejoignez la table avec elle-même en utilisant la colonne home . L'utilisation de LEFT JOIN assure que toutes les lignes du tableau m apparaissent dans le jeu de résultats. Ceux qui n'ont pas de correspondance dans le tableau b auront NULL s pour les colonnes de b .

l'autre condition sur le JOIN demande de faire correspondre seulement les lignes de b qui ont une plus grande valeur sur la colonne datetime que la ligne de m .

en utilisant les données affichées dans la question, le LEFT JOIN produira cette paire:

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

enfin, la clause WHERE ne conserve que les paires qui ont NULL s dans les colonnes de b (elles sont marquées avec * dans le tableau ci-dessus); cela signifie, en raison de la deuxième condition de la clause JOIN , que la ligne choisie de m a la plus grande valeur dans la colonne datetime .

Lire SQL Antipatterns: Éviter les Pièges de la Programmation de Base de données réserver pour d'autres SQL conseils.

53
répondu axiac 2018-03-09 16:39:11

cela fonctionnera même si vous avez deux ou plusieurs lignes pour chaque home avec égal DATETIME 's:

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
25
répondu Quassnoi 2009-03-06 20:30:06

je pense que cela vous donnera le résultat désiré:

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

mais si vous avez besoin d'autres colonnes aussi bien, faites juste une jonction avec la table originale (cochez Michael La Voie réponse)

meilleures salutations.

21
répondu Ricardo Felgueiras 2015-11-26 12:32:15

puisque les gens semblent continuer à courir dans ce fil (la date de commentaire varie de 1,5 année) n'est pas beaucoup plus simple:

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

aucune fonction d'agrégation nécessaire...

santé.

13
répondu MJB 2010-12-05 17:04:14

vous pouvez également essayer celui-ci et pour les grandes tables la performance de requête sera meilleure. Il fonctionne quand il n'y a pas plus de deux enregistrements pour chaque maison et leurs dates sont différentes. Mieux général MySQL requête est un de Michael La Voie ci-dessus.

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

ou dans le cas de Postgres ou ceux dbs qui fournissent des fonctions analytiques essayer

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
9
répondu Shiva 2014-07-16 20:59:00

cela fonctionne sur 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
7
répondu FerranB 2009-03-05 23:19:39
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
6
répondu Kaptah 2009-03-06 20:57:58

essayez ceci pour 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
6
répondu SysDragon 2014-01-17 12:11:29
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)
3
répondu Jr. 2013-03-01 18:51:35

Voici la version MySQL qui n'imprime qu'une seule entrée où il y a des doublons MAX(datetime) dans un groupe.

vous pouvez tester ici http://www.sqlfiddle.com/#!2 / 0a4ae/1

Les Données De L'Échantillon

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

version MySQL avec variable utilisateur

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)

Accepté Réponses " sortie

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)
2
répondu Jason Heo 2014-02-01 09:12:32

une autre façon de gt la ligne la plus récente par groupe en utilisant une requête secondaire qui calcule fondamentalement un rang pour chaque ligne par groupe et puis filtrer vos lignes les plus récentes comme avec le rang = 1

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

DÉMO

voici le démonstration visuelle pour le rang no pour chaque rangée pour une meilleure compréhension

en lisant quelques commentaires qu'en est-il si il y a deux lignes qui ont les mêmes valeurs de champ 'home' et 'datetime'?

requête ci-Dessus échouera et retournera plus de 1 lignes de situation ci-dessus. Pour masquer cette situation, il faudra un autre critère/paramètre/colonne pour décider quelle ligne doit être prise, Quelle est la situation décrite ci-dessus. En regardant l'ensemble de données d'échantillon je suppose qu'il y a une colonne de clé primaire id qui devrait être réglée à l'incrément automatique. Donc on peut utiliser ça colonne pour choisir la ligne la plus récente en modifiant la même requête à l'aide de l'énoncé CASE comme

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

DÉMO

la requête ci-dessus choisira la ligne avec l'id le plus élevé parmi les mêmes datetime valeurs

visual démo pour le rang n de chaque ligne

2
répondu M Khalid Junaid 2017-11-05 13:35:40

Essayez cette

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

en ce qui Concerne K

1
répondu Khb 2009-03-04 20:41:52

pourquoi ne pas utiliser: Sélectionnez home, MAX (datetime) comme MaxDateTime, player, ressource à partir de topten groupe par home Ai-je raté quelque chose?

1
répondu Roland 2015-10-03 10:20:10

c'est la requête dont vous avez besoin:

 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;
0
répondu Vijunav Vastivch 2016-07-14 07:31:33

@Michae la réponse acceptée fonctionnera bien dans la plupart des cas, mais elle échoue pour l'un comme ci-dessous.

dans le cas où il y avait 2 lignes ayant HomeID et Datetime même la requête retournera les deux lignes, Non distinct HomeID comme requis, pour que Ajouter Distinct dans la requête comme ci-dessous.

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
0
répondu Manoj Kargeti 2017-05-08 07:00:53