Welcome to WebmasterWorld Guest from 54.156.56.73

Forum Moderators: open

Message Too Old, No Replies

MySQL - Latest Record per ID

     
6:02 pm on Sep 15, 2005 (gmt 0)

Junior Member

10+ Year Member

joined:May 3, 2003
posts:91
votes: 0


Hi

I am new to the database forum & have just been given new job responsibilities to do some development with mysql - so I am learning it as fast as I can, but need help with a more complex MySQL query - ok here it is:

I have a table (simple example):

ID Name Form Date
2 Bob 45 2005-6-23
3 Jim 42 2005-6-24
3 Bob 41 2005-6-28
2 Jim 47 2005-6-29

I need to query for the latest row by Date for each ID so that only one row for each ID is returned, but it has the latest record by date - so that the following results are returned:

3 Bob 41 2005-6-28
2 Jim 47 2005-6-29

Thanks!

6:36 pm on Sept 15, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member txbakers is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Sept 1, 2001
posts:4392
votes: 0


select max(dateField) from Table group by NameField
6:51 pm on Sept 15, 2005 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12547
votes: 2


I'm assuming those ID's were supposed to match up? Meaning both of Bob's records should have been '2' and Jim's '3'?
SELECT  
ID,
Name,
MAX(Date) as Date
FROM table
GROUP BY ID, Name
ORDER BY Name
;
If you need the other column (or any other column) included in the result set you may need to take a different approach.
Thanks txbakers ;)
8:04 pm on Sept 15, 2005 (gmt 0)

Junior Member

10+ Year Member

joined:May 3, 2003
posts:91
votes: 0


Thanks!

That worked.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members