Possible Duplicate:
Fetch the row which has the Max value for a column
I have an audit table of people and the number of shares they own at a particular timestamp. How do I fetch the most recent record for a given id?
mysql audit table
id name shares dateadded
1 Abc 12 2012-10-06 12:18:21
2 Klm 23 2012-10-06 12:18:21
3 Jim 45 2012-10-06 12:18:21
1 Abc 35 2012-11-06 12:18:21
1 Abc 65 2012-11-17 12:18:21
2 Klm 13 2012-11-06 12:18:21
My desired output :
id name shares dateadded
1 Abc 65 2012-11-17 12:18:21
2 Klm 13 2012-11-06 12:18:21
3 Jim 45 2012-10-06 12:18:21
I could do something like this :
select a.* from audittable a join audittable b
on a.id = b.id
where a.dateadded > b.dateadded;
But that gives me only those most recent records that are repeating. In this case ids 1,2 and not 3. How to get a list of most recent records for all IDs without sub-queries or temp tables?
You will need a subquery, however not subselects (which have a very negative performance hit).
JOIN against a subquery which returns the id and MAX(dateadded) aggregate. The subquery join is needed to be able to match all the other column values in the row containing the latest timestamp.
SELECT
audittable.id,
name,
shares,
audittable.dateadded
FROM
audittable
/* Subquery returns id dateadded grouped by id */
JOIN (
SELECT id, MAX(dateadded) AS dateadded FROM audittable GROUP BY id
/* JOIN condition is on both id and dateadded between the two tables */
) maxtimestamp ON audittable.id = maxtimestamp.id AND audittable.dateadded = maxtimestamp.dateadded