[thelist] MAX and GROUP BY question

Noah St. Amand noah at tookish.net
Wed Oct 24 18:28:21 CDT 2007


Hi,

Using MySQL, I have a table "events" that contains:

event_id
event_date
program_id
client_id
status

The same client and program appear multiple times with different 
statuses (stati?). I need to find all of the clients for whom the most 
recent status for a given program is not 'COMPLETED'.

This query returns the most recent event_date for each client for program 1:

"SELECT MAX(event_date), client_id FROM event WHERE program_id = 1 GROUP 
BY client_id"

When I ask for status, though, in order to omit the 'COMPLETED' records 
programmatically, it returns the status of the oldest record for that 
client in that program, not the latest:

"SELECT MAX(event_date), client_id, status FROM event WHERE program_id = 
1 GROUP BY client_id"

I still get the latest date back, but the status I get is not the one 
from that record, but rather the one from the earliest record for that 
client in that program. How can I get the status associated with the 
latest record?

Thanks for any help,
Noah



More information about the thelist mailing list