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

7 | 13 | 24/12/2008 | borat | 600

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

// 3 ..something exotic: 

With various results.