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

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 ttINNER JOIN    (SELECT home, MAX(datetime) AS MaxDateTime    FROM topten    GROUP BY home) groupedtt ON tt.home = groupedtt.home AND tt.datetime = groupedtt.MaxDateTime

The fastest MySQL solution, without inner queries and without GROUP BY:

SELECT m.*                    -- get the row that contains the max valueFROM topten m                 -- "m" from "max"    LEFT JOIN topten b        -- "b" from "bigger"        ON m.home = b.home    -- match "max" row with "bigger" row by `home`        AND m.datetime < b.datetime           -- want "bigger" than "max"WHERE b.datetime IS NULL      -- keep only if there is no bigger than max

Explanation:

Join the table with itself using the home column. The use of LEFT JOIN ensures all the rows from table m appear in the result set. Those that don't have a match in table b will have NULLs for the columns of b.

The other condition on the JOIN asks to match only the rows from b that have bigger value on the datetime column than the row from m.

Using the data posted in the question, the LEFT JOIN will produce this pairs:

+------------------------------------------+--------------------------------+|              the row from `m`            |    the matching row from `b`   ||------------------------------------------|--------------------------------|| id  home  datetime     player   resource | id    home   datetime      ... ||----|-----|------------|--------|---------|------|------|------------|-----|| 1  | 10  | 04/03/2009 | john   | 399     | NULL | NULL | NULL       | ... | *| 2  | 11  | 04/03/2009 | juliet | 244     | NULL | NULL | NULL       | ... | *| 5  | 12  | 04/03/2009 | borat  | 555     | NULL | NULL | NULL       | ... | *| 3  | 10  | 03/03/2009 | john   | 300     | 1    | 10   | 04/03/2009 | ... || 4  | 11  | 03/03/2009 | juliet | 200     | 2    | 11   | 04/03/2009 | ... || 6  | 12  | 03/03/2009 | borat  | 500     | 5    | 12   | 04/03/2009 | ... || 7  | 13  | 24/12/2008 | borat  | 600     | 8    | 13   | 01/01/2009 | ... || 8  | 13  | 01/01/2009 | borat  | 700     | NULL | NULL | NULL       | ... | *+------------------------------------------+--------------------------------+

Finally, the WHERE clause keeps only the pairs that have NULLs in the columns of b (they are marked with * in the table above); this means, due to the second condition from the JOIN clause, the row selected from m has the biggest value in column datetime.

Read the SQL Antipatterns: Avoiding the Pitfalls of Database Programming book for other SQL tips.

Here goes T-SQL version:

-- Test dataDECLARE @TestTable TABLE (id INT, home INT, date DATETIME,   player VARCHAR(20), resource INT)INSERT INTO @TestTableSELECT 1, 10, '2009-03-04', 'john', 399 UNIONSELECT 2, 11, '2009-03-04', 'juliet', 244 UNIONSELECT 5, 12, '2009-03-04', 'borat', 555 UNIONSELECT 3, 10, '2009-03-03', 'john', 300 UNIONSELECT 4, 11, '2009-03-03', 'juliet', 200 UNIONSELECT 6, 12, '2009-03-03', 'borat', 500 UNIONSELECT 7, 13, '2008-12-24', 'borat', 600 UNIONSELECT 8, 13, '2009-01-01', 'borat', 700-- AnswerSELECT id, home, date, player, resource FROM (SELECT id, home, date, player, resource,     RANK() OVER (PARTITION BY home ORDER BY date DESC) N    FROM @TestTable)M WHERE N = 1-- and if you really want only home with max dateSELECT T.id, T.home, T.date, T.player, T.resource     FROM @TestTable TINNER JOIN (   SELECT TI.id, TI.home, TI.date,         RANK() OVER (PARTITION BY TI.home ORDER BY TI.date) N    FROM @TestTable TI    WHERE TI.date IN (SELECT MAX(TM.date) FROM @TestTable TM))TJ ON TJ.N = 1 AND T.id = TJ.id

EDIT
Unfortunately, there are no RANK() OVER function in MySQL.
But it can be emulated, see Emulating Analytic (AKA Ranking) Functions with MySQL.
So this is MySQL version:

SELECT id, home, date, player, resource FROM TestTable AS t1 WHERE     (SELECT COUNT(*)             FROM TestTable AS t2             WHERE t2.home = t1.home AND t2.date > t1.date    ) = 0