MySQL - Latest Record per ID

[cayenne]

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]

select max(dateField) from Table group by NameField

[coopster]

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]

Thanks!

That worked.