Programmer - SQL: SELECT rows with MAX(Column value), DISTINCT by another column?

Programmer Question

Hi all

My table is:

• id home datetime player resource


• ------|------------|----------------|-----------|------------


• 1 10 04/03/2009 john 399


• 2 11 04/03/2009 juliet 244


• 3 12 04/03/2009 borat 555


• 4 10 03/03/2009 john 300


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


• 3 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 daytime
ORDER BY daytime DESC

Doesn't work. Result-set has 130 rows allthough database holds 187.
Result includes some dublicates 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 daytime

Nope. Gives all the records.
In addition it's not sorted as wished.

// 3 ..something exotic:

with various results

Any help is highly appreciated.
Thanks in advance,
Kaptah