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
>

Untested, and knocked off the top of my head, but try this. It should give you the general idea. Standard disclaimers apply.

select
  invest_id,
  market_price
from
  investments i,
  market_price_hist p1,
  (select invest_id, max(price_date) price_date     from market_price_hist
    group by invest_id) p2
where

  p1.invest_id = i.invest_id and
  p2.invest_id = i.invest_id and
  p1.price_date = p2.price_date

Note that this assumes that no investment has two identical price dates.

Also, if you want to show investments that have no prices in the history table, you need to make it an outter join. (You didn't say what you wanted to do in this case).

--
Chuck Hamilton
chuckh_at_dvol.com

This message delivered by electronic sled dogs. WOOF!