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