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.
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
Test it for distinct, if two equal max datetime be in the same home (with different players)
I think the classic way to do this is with a natural join: "SELECT tt.* FROM topten tt NATURAL JOIN ( SELECT home, MAX(datetime) AS datetime FROM topten GROUP BY home ) mostrecent;" Same query exactly, but arguably more readable
what about if there are two rows which have same 'home' and 'datetime' field values?
@Young the problem with your query is that it may return
id
,player
andresource
of non-max row for a given home i.e. for home = 10 you may get :3 | 10 | 04/03/2009 | john | 300
In other words it doesn't guarantees that all column of a row in resultset will belong to max(datetime) for given home.@me1111 problem with your query is that it may/may not return row ith max(datetime) for a given home. Reason being GROUP BY will fetch any random row for each home and ORDER BY will just sort the overall all result as produced by GROUP BY