Re: Oracle SQL Problem

On 18 Nov 2005 12:54:03 -0800, "heprox" <srichards_at_wss-footwear.com> wrote:

>I have a table called GM_PRC with the following data:
>
>
>SKU_NUM ITM_CD BEG_DT RET_PRC
>----------- -------- --------- --------
>000000898-01 000000898 2003-12-03 56.99
>000000898-02 000000898 2003-12-03 56.99
>000000898-03 000000898 2003-12-03 56.99
>000000898-04 000000898 2003-12-03 56.99
>000000898-05 000000898 2003-12-03 56.99
>000000898-28 000000898 2003-12-29 56.99
>000000898-30 000000898 2005-01-31 56.99
>000000898-01 000000898 2005-05-11 58.99
>000000898-02 000000898 2005-05-11 58.99
>000000898-03 000000898 2005-05-11 58.99
>000000898-04 000000898 2005-05-11 58.99
>000000898-05 000000898 2005-05-11 58.99
>000000898-28 000000898 2005-05-11 58.99
>000000898-30 000000898 2005-05-11 58.99
>
>
>...I want to select the most recent price for the ITM_CD (which is
>comprised of all of the SKU_NUM's below it). I'm using the following:
>
>select max(beg_dt) beg_dt,itm_cd,ret_prc from gm_prc
>where itm_cd = '000000898'
>group by itm_cd,ret_prc order by beg_dt
>
>
>...but I get:
>
>
>BEG_DT ITM_CD RET_PRC
>------------------- ------------ ------------
>2005-01-31 00:00:00 000000898 56.99
>2005-05-11 00:00:00 000000898 58.99
>
>2 Row(s) affected
>
>
>...what am I doing wrong? I should only get one line here for the
>"2005-05-11" date?
>

My understanding is that you want the most recent price for each ITM_CD. Right ?

In that case, this query, which uses a correlated subquery (and is one out of several possible solutions), will do the work :

select itm_cd,ret_prc,beg_dt
from gm_prc b where exists
( select 1 from
(select itm_cd,max(beg_dt) as beg_dt
from gm_prc
group by itm_cd) a
where a.itm_cd = b.itm_cd
  and a.beg_dt = b.beg_dt);

• Kenneth Koenraadt