How to retrieve most recent record from a list of records

How to retrieve most recent record from a list of records

  
Hi guys,

I have this quick question for I'd like some guidance.

I have this application where I'm listing all the records from a table which as a foreign key linked to another table. It is quite a simple table, only has Name and Datechanged, p.e.

When I'm listing the contents it is (ab)normal that I get duplicate rows for the same person:

John Doe | 1-1-2011
John Doe | 1-12-2011
Mary Jane | 23-5-2011
Mary Jane | 12-9-2011

I would like the list only to show the latest records for each person:

John Doe | 1-12-2011
Mary Jane | 12-9-2011

Can it be done via a simple query, or the only way to achieve this is using advanced queries?

Thanks,
Nuno
Hi Nuno.

I think your problem is asking for an Advanced Query.  I haven't tested it but I think you can do something like this:

SELECT DISTINCT PersonId, max(DateChanged)
FROM Person
GROUP BY PersonId.

Just add the join to the other table and it should give the most recent record for each person.
João,

Thanks for the reply. I was trying to avoid going that way, but since that seems to be the only solution, well gotta go for it then.

Thanks again,
Nuno Silva