Kaptah Kaptah - 4 months ago 21
SQL Question

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

My table is:



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


I need to select each distinct
home
holding the maximum value of
datetime
.

Result would be:



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


I have tried:

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


Doesn't work. Result-set has 130 rows although database holds 187.
Result includes some duplicates of
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. Gives all the records.

-- 3 ..something exotic:


With various results.

Answer

You are so close! All you need to do is select BOTH the home and it's max date time, then join back to the topten table on BOTH fields:

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