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

Question

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 1

You are so close! All you need to do is select BOTH the home and its 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