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
1Answer
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
- answered 8 years ago
- G John
Your Answer