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

    Question Unanswered: SQL Statement Problem

    I have a table called GM_PRC 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
    Dec 2003
    Posts
    1,074
    try:

    Code:
    select beg_dt,itm_cd,ret_prc from gm_prc
    from gm_prc t1
    where itm_cd = '000000898' and 
             beg_date = (select max(beg_date) 
                               from gm_prc t2
                               where t2.itm_cd = t1.itm_cd)

  3. #3
    Join Date
    Oct 2003
    Posts
    54
    The statement:

    Code:
    select beg_dt,itm_cd,ret_prc from gm_prc
    from gm_prc t1
    where itm_cd = '000000898' and 
             beg_date = (select max(beg_date) 
                               from gm_prc t2
                               where t2.itm_cd = t1.itm_cd)
    ...had errors, so I corrected it too:

    Code:
    select beg_dt,itm_cd,ret_prc 
    from gm_prc t1
    where itm_cd = '000000898' and 
             beg_dt = (select max(beg_dt) 
                               from gm_prc t2
                               where t2.itm_cd = t1.itm_cd)
    ...and got the following:

    Code:
    BEG_DT              ITM_CD       RET_PRC      
    ------------------- ------------ ------------ 
    2005-05-11 00:00:00 000000898    58.99        
    2005-05-11 00:00:00 000000898    58.99        
    2005-05-11 00:00:00 000000898    58.99        
    2005-05-11 00:00:00 000000898    58.99        
    2005-05-11 00:00:00 000000898    58.99        
    2005-05-11 00:00:00 000000898    58.99        
    2005-05-11 00:00:00 000000898    58.99        
    2005-05-11 00:00:00 000000898    58.99        
    2005-05-11 00:00:00 000000898    58.99        
    2005-05-11 00:00:00 000000898    58.99        
    2005-05-11 00:00:00 000000898    58.99        
    2005-05-11 00:00:00 000000898    58.99        
    2005-05-11 00:00:00 000000898    58.99        
    2005-05-11 00:00:00 000000898    58.99        
    2005-05-11 00:00:00 000000898    58.99        
    
    15 Row(s) affected
    ...which is a little closer except that we are reporting every SKU_NUM in the table when what I need is just the single line for the ITM_CD like:

    Code:
    2005-05-11 00:00:00 000000898    58.99

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    I'd think you'd want something other than the following, but:

    Code:
    select beg_dt,itm_cd,ret_prc 
    from gm_prc t1
    where itm_cd = '000000898' and 
             beg_dt = (select max(beg_dt) 
                               from gm_prc t2
                               where t2.itm_cd = t1.itm_cd) and
             rownum < 2
    What if you have records like:
    Code:
    BEG_DT              ITM_CD       RET_PRC      
    ------------------- ------------ ------------ 
    2005-05-11 00:00:00 000000898    58.99     
    2005-05-11 00:00:00 000000898    58.99     
    2005-05-11 00:00:00 000000898    55.68        
    2005-05-11 00:00:00 000000898    55.68
    Would you want to see both the ones with different RET_PRC values? The above SQL arbitrarily picks one record (although you could throw in an ORDER BY to get one over the other).

    -cf

  5. #5
    Join Date
    Oct 2003
    Posts
    54
    We already validate that all SKU_NUM records with the same ITM_CD have the same RET_PRC for the same BEG_DT. This database stores RET_PRC at the SKU level however I want to report the RET_PRC at the ITM_CD level.

Posting Permissions

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