Re: Oracle SQL Problem

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 18 Nov 2005 22:23:05 +0100
Message-ID: <437e463a$0$4286$626a14ce@news.free.fr>

"heprox" <srichards_at_wss-footwear.com> a écrit dans le message de news: 1132347243.922108.60820_at_g14g2000cwa.googlegroups.com...
|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?
|

Have a look at rank analytic function:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions95a.htm#1003189

Regards
Michel Cadot Received on Fri Nov 18 2005 - 15:23:05 CST