问选择每个不同的home持有最大值datetime？

我的表是：

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

我需要选择每个不同的home持有最大值datetime。

结果将是：

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

我努力了：

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

不起作用。结果集有130行，尽管数据库保存了187.结果包括一些重复的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 

不。给所有的记录。

-- 3 ..something exotic:

有不同的结果。