Como posso SELECCIONAR linhas com MAX(Column value), DISTINCT por outra coluna em SQL?

A minha mesa é:

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

Preciso selecionar cada home distinto com o valor máximo de datatime.

Resultado seria:

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

Tentei:

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

Não... 39; não funciona. O conjunto de resultados tem 130 linhas, embora a base de dados tenha 187. O resultado inclui algumas duplicatas 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

Não. Dá todos os registos.

-- 3 ..something exotic:

Com vários resultados.

DineshDB
Pergunta editada :em
Programação
sql mysql greatest-n-per-group max distinct
4º março 2009 в 8:14
22 vistas
Solução

Você está tão perto! Tudo o que você precisa fazer é selecionar AMBOS a casa e sua hora máxima de data, depois volte para a tabela de topten nos campos AMBOS:

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
Resposta editada :em
Comentários (22)

Isto vai funcionar mesmo que você tenha duas ou mais linhas para cada home com igual 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
Resposta editada :em
Comentários (8)

Acho que isto vai dar-lhe o resultado desejado:

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

**MAS*** se você precisar de outras colunas também, basta fazer uma junção com a tabela original (verifique a resposta Michael La Voie)

Com os melhores cumprimentos.

sactiw
Resposta editada :em
Comentários (3)