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

TAGS : | | | | |