Hi everyone,
In my database a have a list of persons whose data is updated from time to time, but not all of it necessarily the same day. So for example, it would show something like:
USER NAME SCORE UPDATE
1 V 5 30/1/2011
1 V 3 23/2/2011
1 V 2 3/4/2011
5 P 8 27/1/2011
5 P 6 17/4/2011
6 J 3 22/3/2011
6 J 4 24/4/2011
I need to do a query that brings back the last record for each user:
1 V 2 3/4/2011
5 P 6 17/4/2011
6 J 4 24/4/2011
I tried grouping by user and getting the max(update), but the SCORE doesn’t necessarily need to be the highest one, just the one that matches for that date.
Could someone give me hint on this? I am guessing I am going to need a subquery, but I don’t see it very clear!
Hi Mike!
Thanks, I think that’s where I want to get to although if I am not mistaken, aggregate functions cannot appear in a subquery, so it possitively returns an error. Is there a way to overcome this?
Thank you
Well, since SCORE is also a primary key in my table (there are a bunch of other columns) it would bring older dates when they have different scores. Does that make sense?
I am still checking this beacuse it must be some error with my code, since it tells me the following error:
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause od a select list, and the column being aggregated is an outer reference.
I have checked that the MAX is in the subquery, and not in the WHERE, but I must be missing something :S