need help with MySQL query

[A@P]

Having a hard time coming up with a working query, thought I would seek some help!

 

Here's the situation, say this is my dB:

 

serial_number        date        stage

 

      001            20080909      in

      002            20080909      in

      003            20080911      in

      002            20080910      out

      001            20080910      out

      003            20080912      out

      004            20080915      in

      002            20080911      left

      003            20080916      left

 

I need to find all serial_number that at the most recent date is at stage 'X'. So if X were "out", the only matching result from the above dB would be serial_number "001".

 

Hope I explained it well enough. Any help? Thanks in advance!

[Barand]

try

SELECT serial_number
FROM stages s
JOIN
(SELECT serial_number,MAX(`date`) as `date` FROM stages GROUP BY serial_number) as A
USING (serial_number, `date`)
WHERE s.stage = 'out'