there are a couple of diffent ways to do this, try this one
select p.name,
o.stomach_object_id,
o.date_found,
o.description,
from patient p,
stomach_object o,
(select patient_id,max(date_found) d1 from stomach_object group by
patient_id) inline
where p.patient_id=o.patient_id
and (o.patient_id = inline.patientid and o.date_found = inline.d1)
if you wish to do it using a corealated subquery yuo would do this
select p.name,
o.stomach_object_id,
o.date_found,
o.description,
from patient p,
stomach_object o
where p.patient_id=o.patient_id
and o.date_found = (select max(date_found) stomach_objects where
patient_id = o.patient_id)
But I suspect that the first syntax will rin quicker
HTH
Conan
