Mike Yearwood
Toronto, Ontario, Canada
General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>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
Thanks for the reply. I'll try it.
Pat Moran
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only