Hi. I have a table we'll call PhoneTable:

ID Phone LastUpdated

999999 555-1234 1/1/01
999999 555-3456 4/21/02
999999 555-7890 9/18/04
999998 555-9998 4/20/05

I'd like the output to give me only the most recently updated records, so, the highest LastUpdated date for each ID:

999999 555-7890 9/18/04
999998 555-9998 4/20/05

Here's what I tried:
SELECT ID, Phone, MAX(LastUpdated)
FROM PhoneTable
GROUP BY ID, Phone, LastUpdated

But it's still giving me all of the rows. What am I missing?

Thanks!

Try using the DISTINCT clause instead with an ORDER BY on the date:

SELECT ID, Phone, DISTINCT LastUpdated
FROM PhoneTable
ORDER BY LastUpdated

Michael J

Thanks. I tried that but it still returns all of the rows, just in order of the most recent dates first. I still have the problem of dupes. If I select distinct on only the LastUpdated dates or only on IDs I get unique values but when I add LastUpdated to select it gives me duplicates. Any other ideas?

If using distinct is giving you dupes, then add a group by. I'd try using distinct just on the LastUpdated and group by that as well.

Michael J

Try this

SELECT TOP 1 ID, Phone
FROM PhoneTable
ORDER BY LastUpdated DESC

This returns the records with the most recent first and the TOP function gives you only the first record. It works in SQL Server (and should do in Access although I havent tested it), if you are using MySQL or Oracle use the LIMIT function instead of TOP.