select max

[ddtpmyra]

my clause is like this (below)...
what I'm trying to do is to select the most current patient appointment.

from 
person per 
left join patient p on p.person_id  =per.person_id 
left join person_payer payer on payer.person_id=per.person_id  and payer.def_cob =1
left join person_ud pud on pud.person_id =per.Person_id
left join mstr_lists ml on ml.mstr_list_item_id =pud.ud_demo3_id  
left join appointments a on a.person_id =per.person_id and a.appt_date (SELECT MAX(appt_date) FROM appointments)

but I'm not getting what I want. I'm having duplicates because a person has multiple appointments and what all I want is to get the max appt. Or should I put it on my select before the from? Please tell me how.

thanks

[Rabbit]

Join to a subquery to that returns the max date per patient.

[ck9663]

Or use a CTE to get the max appt date per personid and use it to join to your query.

Happy Coding!!!


~~ CK