Computing.Net > Forums > Web Development > SQL select most recent records

Computer Problems? Computing.Net has over 1,000,000 posts about all things technology related! Over 90% answered within 24 hours! Click here to start participating now! Also, be sure to check out the New User Guide.

SQL select most recent records

Reply to Message Icon

Name: Shmanda
Date: April 12, 2005 at 13:08:51 Pacific
OS: WinXPPro
CPU/Ram: plenty
Comment:

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!



Sponsored Link
Ads by Google

Response Number 1
Name: Michael J (by mjdamato)
Date: April 12, 2005 at 13:54:13 Pacific
Reply:

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


0

Response Number 2
Name: Shmanda
Date: April 12, 2005 at 14:23:33 Pacific
Reply:

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?


0

Response Number 3
Name: Michael J (by mjdamato)
Date: April 12, 2005 at 21:34:53 Pacific
Reply:

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


0

Response Number 4
Name: Euan
Date: May 26, 2005 at 06:31:17 Pacific
Reply:

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.


0

Sponsored Link
Ads by Google
Reply to Message Icon

Related Posts

See More







Post Locked

This post is quite old and has been locked from receiving new replies. Please create a new posting instead.


Go to Web Development Forum Home


Sponsored links

Ads by Google


Results for: SQL select most recent records

Querying Oracle from ASP www.computing.net/answers/webdevel/querying-oracle-from-asp/2046.html

ASP (or computer) Order Problem www.computing.net/answers/webdevel/asp-or-computer-order-problem/397.html

Beginner's TextBox and Gridview que www.computing.net/answers/webdevel/beginners-textbox-and-gridview-que/3799.html