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

In MySQL 8.0 this can be achieved efficiently by using row_number() window function with common table expression.

(Here row_number() basically generating unique sequence for each row for every player starting with 1 in descending order of resource. So, for every player row with sequence number 1 will be with highest resource value. Now all we need to do is selecting row with sequence number 1 for each player. It can be done by writing an outer query around this query. But we used common table expression instead since it's more readable.)

Schema:

create TABLE TestTable(id INT, home INT, date DATETIME,
player VARCHAR(20), resource INT);
INSERT INTO TestTable
SELECT 1, 10, '2009-03-04', 'john', 399 UNION
SELECT 2, 11, '2009-03-04', 'juliet', 244 UNION
SELECT 5, 12, '2009-03-04', 'borat', 555 UNION
SELECT 3, 10, '2009-03-03', 'john', 300 UNION
SELECT 4, 11, '2009-03-03', 'juliet', 200 UNION
SELECT 6, 12, '2009-03-03', 'borat', 500 UNION
SELECT 7, 13, '2008-12-24', 'borat', 600 UNION
SELECT 8, 13, '2009-01-01', 'borat', 700

Query:

with cte as
(
select id, home, date , player, resource,
Row_Number()Over(Partition by home order by date desc) rownumber from TestTable
)
select id, home, date , player, resource from cte where rownumber=1

Output:

id | home | date | player | resource
-: | ---: | :------------------ | :----- | -------:
1 | 10 | 2009-03-04 00:00:00 | john | 399
2 | 11 | 2009-03-04 00:00:00 | juliet | 244
5 | 12 | 2009-03-04 00:00:00 | borat | 555
8 | 13 | 2009-01-01 00:00:00 | borat | 700


*db<>fiddle [here](

[To see links please register here]

)*