I have two tables:

patients: with single record per patient and
referrals: with any number of referral records per patient. Each referall record has the patient id field (MNO - relating to MNO field in patients table) as well as a referral date (refdate) field

I want an Select SQL statement which will list all patients to the left of the join and, on the right of the join, ONLY the latest referral record relating to each the patient - i.e. if the patient has 10 referral records, only the most recent to be included.

Is this possible in SQL? Any ideas Gratefully Received!