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.
Solution:
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
http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql