I would like to create a query that looks through a table, and for each unique key in one field, find the newest corresponding date located in another field.
If I have a table such as:
tblAppointment
--------------------------
---
ID|Name| Date
-----------------------
1 | ptn1 | 3/5/2008
2 | ptn1 | 1/8/2007
3 | ptn2 | 1/2/2006
4 | ptn2 | 2/5/2008
5 | ptn3 | 3/7/2008
6 | ptn3 | 2/5/2009
Is there a query that will return:
ID|Name| Date
1 | ptn1 | 3/5/2008
4 | ptn2 | 2/5/2008
6 | ptn3 | 2/5/2009
One way I was thinking, which doesn't work, would be something like:
SELECT DISTINCT ID, Name, Date FROM (SELECT * FROM tblAppointment ORDER BY Date) as SortedTable
The idea was to get a subquery with all the dates sorted, then do a distinct, hoping that the distinct would go in order of the ordered table, so it would choose the newest dates for each Name. Of course this doesn't work, but thats kind of the idea behind what I want.
Start Free Trial
