homepage Welcome to WebmasterWorld Guest from 54.198.42.105
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
MySQL - Latest Record per ID
cayenne




msg:1578809
 6:02 pm on Sep 15, 2005 (gmt 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!

 

txbakers




msg:1578810
 6:36 pm on Sep 15, 2005 (gmt 0)

select max(dateField) from Table group by NameField

coopster




msg:1578811
 6:51 pm on Sep 15, 2005 (gmt 0)

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 ;)

cayenne




msg:1578812
 8:04 pm on Sep 15, 2005 (gmt 0)

Thanks!

That worked.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved