Toad World® Forums

Retrieving latest records

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!

Thank you very much

Message was edited by: Teleki

do this

select user,name,max(update)

from Tabl_name

group by user,name

good luck

Thank you for your answer rivasquez, but I also need the info of the SCORE corresponding to the last date :S
Can you think of something?

Select * From Tabl_Name t1 Where update = (select Max(upate) From Tabl_name t2
Where t1.user= t2.user )

Mike

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

Hi,

The query should work. I actually wrote a query using similar logic today.

Subqueries can contain aggregate functions. This page shows another example of
using an aggregate in a subquery…
http://www.java2s.com/Code/SQLServer/Subquery/Useaggregatefunctioninsubquery.htm

Mike

I create a table using your data and this has to work, run it and tell me what do you get

select user,name,max(update),score

from Tabl_name

group by user,name,score

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

THank you