[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