Welcome to WebmasterWorld Guest from 54.226.183.49

Forum Moderators: open

Message Too Old, No Replies

MySQL - Latest Record per ID

     

cayenne

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

10+ Year Member



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

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

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



select max(dateField) from Table group by NameField

coopster

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

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



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

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

10+ Year Member



Thanks!

That worked.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month