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

Databases Forum

    
MySQL - Latest Record per ID
cayenne

10+ Year Member



 
Msg#: 35 posted 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

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



 
Msg#: 35 posted 6:36 pm on Sep 15, 2005 (gmt 0)

select max(dateField) from Table group by NameField

coopster

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



 
Msg#: 35 posted 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

10+ Year Member



 
Msg#: 35 posted 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