Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    54

    Question Unanswered: SQL Statement Problem

    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?

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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
    Last edited by Peter.Vanroose; 11-18-05 at 18:20.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •