1

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?

Community
  • 1
  • 1
ThinkCode
  • 7,841
  • 21
  • 73
  • 92
  • Is it a theoretical question? Why not subqueries? The answer by @Michael is probably the fastest way, with proper indexes. – ypercubeᵀᴹ Nov 21 '12 at 17:31
  • Not theoretical at all. I was not thinking right! Marc's earlier answer works too select a.*, max(dateadded) from audittable group by id; Isn't it? – ThinkCode Nov 21 '12 at 17:35
  • Bill, possibly. I feel my question is well laid out for others to read and understand. The other link is for Oracle. – ThinkCode Nov 21 '12 at 17:46

2 Answers2

5

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

Here is a demonstration on SQLfiddle

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • 1
    The op doesn't want subqueries – Marc Nov 21 '12 at 17:29
  • Marc, I completely was not thinking. Your earlier solution works just fine. I am oOK with Max and Groupby. As long as I get all the records (repeating or not) just once based on most recent timestamp. – ThinkCode Nov 21 '12 at 17:31
  • @Marc This can't be done without a subquery. The OP may not want subselects, which have a very different performance characteristic than a joined subquery. – Michael Berkowski Nov 21 '12 at 17:31
  • Michael, I would like to know why Marc's earlier solution doesn't work in this case? I thought it just works, no? – ThinkCode Nov 21 '12 at 17:33
  • 1
    @ThinkCode Because it only returns the id and dateadded, not the other values. And if you just add the other values into the SELECT list, you won't deterministically get the correct shares back. MySQL will allow you to do it (which other RDBMS would not), but the results won't always be correct. – Michael Berkowski Nov 21 '12 at 17:33
  • 1
    No cause I group by only by id. So you can't have other columns except if you use a subquery like in this answer – Marc Nov 21 '12 at 17:33
  • @ThinkCode: Marc's query will not show all columns. – ypercubeᵀᴹ Nov 21 '12 at 17:33
  • select a.*, max(dateadded) from audittable group by id; Doesn't it show all columns? – ThinkCode Nov 21 '12 at 17:36
  • 2
    @ThinkCode It shows all columns, but look at http://sqlfiddle.com/#!2/5aa01/3 It will list 12 shares for id=1, when it should list 65. You can't just add other columns into the SELECT list. – Michael Berkowski Nov 21 '12 at 17:36
  • Got it, thank you so much guys for the explanation. Completely overlooked that one. – ThinkCode Nov 21 '12 at 17:37
2

Without subqueries, you are limited to this

SELECT id, MAX(dateAdded)
FROM audittable
GROUP BY id

If you want the other columns, you need a subquery like Michael Berkowski's answer

Marc
  • 16,170
  • 20
  • 76
  • 119