Please help with query

[leetee]

I have the below query which pulls out what I want with one expection and I would be grateful if anyone can suggest how to solve it.

 

The joblist is the main table which links to the events table.  A job_id can be entered into the events table many times.  I need to pull out all job ids that are entered into the events list but only pull out each distinct job which was the most recent entry (according to the date).  At the moment, the query is pulling out the earliest job_id entered into the events table.

 

SELECT j.id,j.jobref,DATE_FORMAT(e.date, '%m/%Y') AS date FROM joblist as j
INNER JOIN events as e ON j.id=e.job_id
WHERE e.status_id = '9'
GROUP BY j.jobref
ORDER by j.jobref

[fenway]

You need to do this in two steps... first, get the unique jobs with the most recent date... then join this derived table back.