SQL Statement Problem

heprox · #1 (**permalink**) 11-18-05, 15:51

I have a table called GM_PRC on Oracle 8i with the following data:

Code:

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:

Code:

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:

Code:

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?

Peter.Vanroose · #2 (**permalink**) 11-18-05, 17:12

Try the following:

Code:

SELECT beg_dt, itm_cd, ret_prc
FROM   gm_prc AS g
WHERE  beg_dt = (SELECT MAX(beg_dt) FROM gm_prc WHERE itm_cd = g.itm_cd)
ORDER BY beg_dt

Internet Services

Web Development

Digital Marketing

Consumer Tech