Question Comment puis-je sélectionner des lignes avec MAX (valeur de colonne), DISTINCT par une autre colonne dans 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 tenant la valeur maximale de datetime.

Le résultat serait: 

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 fonctionne pas Le jeu de résultats contient 130 lignes, bien que la base de données en contienne 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 

Nan. Donne tous les enregistrements.

-- 3 ..something exotic: 

Avec divers résultats.


643
2018-03-04 20:14


origine


Réponses:


Vous êtes si proche! Tout ce que vous devez faire est de sélectionner à la fois la maison et son heure de la date maximale, puis rejoindre 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

789
2018-03-04 20:22



Voici 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 fonction RANK () OVER dans MySQL.
Mais il peut être émulé, voir Émulation des fonctions analytiques (classement AKA) avec MySQL.
Alors ceci 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

67
2018-03-04 20:59



Le plus rapide MySQL solution, 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:

Joindre la table avec lui-même en utilisant le home colonne. L'utilisation de LEFT JOIN assure toutes les lignes de la table m apparaissent dans le jeu de résultats. Ceux qui n'ont pas de correspondance dans le tableau b aura NULLs pour les colonnes de b.

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

En utilisant les données affichées dans la question, le LEFT JOIN produira ces paires:

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

Finalement, le WHERE clause conserve seulement les paires qui ont NULLs dans les colonnes de b (Ils sont marqués avec * dans le tableau ci-dessus); cela signifie, en raison de la deuxième condition de la JOIN clause, la ligne sélectionnée m a la plus grande valeur dans la colonne datetime.

Lis le Antipatterns SQL: éviter les pièges de la programmation de base de données livre pour d'autres astuces SQL.


50
2018-01-06 16:23



Cela fonctionnera même si vous avez deux lignes ou plus pour chaque home avec égalité DATETIMEde:

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

24
2018-03-04 20:24



Je pense que cela vous donnera le résultat souhaité:

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

MAIS Si vous avez besoin d'autres colonnes, faites simplement une jointure avec la table d'origine (vérifiez Michael La Voie répondre)

Meilleures salutations.


20
2018-03-04 20:30



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

À votre santé.


12
2017-12-05 17:04



Vous pouvez également essayer celui-ci et pour les grandes tables, les performances des requêtes seront meilleures. Cela fonctionne quand il n'y a pas plus de deux enregistrements pour chaque maison et leurs dates sont différentes. Une meilleure requête MySQL générale est celle 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 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

8
2018-02-16 20:51