Re: Outer Join - Solution Needed

KMAQ65A_at_prodigy.com (Shivakumar Rajagopal) wrote:
>investment is a table with invest_id as a key field. I need to list all
>invest_ids with their latest market price from market_price_hist. Mind
>you every invest_id need not be in market_price_hist. Also there are
>multiple rows for each invest_id in market_price_hist.
>
>Shivakumar
>
>srajago774_at_aol.com
>

Hi there

Try the following.

select i.invest_id, m.market_price
from investment i, market_price_hist m
where i.invest_id = m.invest_id (+)
and m.date_field in ( select max(date_field)

                      from market_price_hist m2
                      where m2.invest_id = m1.invest_id)

I have not tried this query, but I have a feeling that it should answer your question. In case of any problems, please let me know.

Regards

N.Prabhakar Received on Sun Dec 08 1996 - 00:00:00 CET