I have a table which keeps track of the status of members. In the table is
member_id int(8)
status_code char(1)
status_date date
KEY member_id (member_id,status_code,status_date)
Each member can have multiple records because a record is added each time
the status changes but the old record is kept for history.
What I want to do is find the latest status for each member. Actually I want
to find all those with an status of "A". But it must be the current (latest)
status. How do I find the most current date for each member in a pile of
many records for many members with many status settings with one SQL
statement? This is a bit beyond my capabilities so I am asking for help.
My initial SQL is
SELECT * FROM memberstatus WHERE status_code = 'a'
but that is my limit. I know an AND comes next but need help after that! I
know that MAX is not for dates so that is out. Is there a LATEST DATE? I did
not see one when I was looking at the date functions.
Appreciate the help.
Mike