Jak mogę wyszukać wiersze z MAX(wartość kolumny), DISTINCT przez inną kolumnę w SQL?

Mój stół to:

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

Muszę wybrać każdy odrębny home posiadający maksymalną wartość datetime.

Wynikiem byłoby:

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

Próbowałem:

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

Nie działa. Zestaw wyników ma 130 wierszy, choć baza danych ma ich 187. Wynik zawiera kilka duplikatów 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

Nope. Daje wszystkie rekordy.

-- 3 ..something exotic:

Z różnymi wynikami.

DineshDB
Edytowane pytanie 26. marca 2018 в 9:58
Programowanie
sql mysql greatest-n-per-group max distinct
4. marca 2009 в 8:14
26 opinie
Rozwiązanie

Jesteś tak blisko! Wszystko, co musisz zrobić, to wybrać OBA domy i ich maksymalny czas daty, a następnie dołączyć z powrotem do tabeli topten na obu polach:

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
Andreas Rejbrand
Edytowana odpowiedź 14. lutego 2018 в 12:06
Komentarze (22)

To będzie działać nawet jeśli masz dwa lub więcej wierszy dla każdego home z równymi 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
Quassnoi
Edytowana odpowiedź 6. marca 2009 в 8:30
Komentarze (8)

Myślę, że to da ci pożądany rezultat:

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

ALE jeśli potrzebujesz również innych kolumn, po prostu wykonaj połączenie z oryginalną tabelą (sprawdź Michael La Voie odpowiedź)

Z najlepszymi pozdrowieniami.

sactiw
Edytowana odpowiedź 26. listopada 2015 в 12:32
Komentarze (3)