How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
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 |
| 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 datetime
ORDER BY datetime DESC
Doesn't work. Result-set has 130 rows although database holds 187.
Result includes some duplicates 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 datetime
Nope. Gives all the records.
-- 3 ..something exotic:
With various results.