How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
distinct, greatest-n-per-group, max, mysql, sql
Solution
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 tt
INNER JOIN
(SELECT home, MAX(datetime) AS MaxDateTime
FROM topten
GROUP BY home) groupedtt
ON tt.home = groupedtt.home
AND tt.datetime = groupedtt.MaxDateTime
Problem
I have a table of player performance: ``` CREATE TABLE TopTen ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, home INT UNSIGNED NOT NULL, `datetime`DATETIME NOT NULL, player VARCHAR(6) NOT NULL, resource INT NOT NULL ); ``` What query will return the rows for each distinct `home` holding its maximum value of `datetime`? In other words, how can I filter by the maximum `datetime` (grouped by `home`) and still include other non-grouped, non-aggregate columns (such as `player`) in the result? For this sample data: ``` INSERT INTO TopTen (id, home, `datetime`, player, resource) VALUES (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) ; ``` the result should 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 8 13 01/01/2009 borat 700 I tried a subquery getting the maximum `datetime` for each `home`: ``` -- 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 ``` The result-set has 130 rows although database holds 187, indicating the result includes some duplicates of `home`. Then I tried joining to a subquery that gets the maximum `datetime` for each row `id`: ``` -- 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` ``` Nope. Gives all the records. I tried various exotic queries, each with various results, but nothing that got me any closer to solving this problem.