Hi Patrick.

I just went through a similar situation. If you need any fields from the history of admissions, neither your original approach nor Ed's will work. You must use 2 queries. The first gives the patient id and the max admission date. The second uses the information in the first to join the patient and the admission history pulling only the record from the admission history that has that maximum admission date.

I had forgotten that SQL seems to position the record pointer on the last of the history records after performing the MAX(). Larry Miller (who I worked with at Metamor -- the makers of Visual MaxFrame Professional) graciously reminded me of this (in public <g>).

Here's Larry's original solution for you to chew on...

select ;
parts.part_pk, ;
max(nvl(price_history.effective,dtot({}))) as Effective_Date ;
from parts ;
left outer join price_history on ;
parts.part_pk = price_history.part_fk ;
group by parts.part_pk into c_temp

select ;
c_temp.part_pk, ;
c_temp.Effective_Date, ;
nvl(price_history.Price,0000000.00) as Current_Price ;
from c_temp ;
left outer join price_history on ;
c_temp.part_pk = price_history.part_fk ;
where c_temp.Effective_Date = price_history.effective

I hope that helps. It seems this technique should be documented somewhere in a FAQ.

HTH