1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Latest Record SQL

Discussion in 'SQL PL/SQL' started by Jersey526, Aug 22, 2010.

  1. Jersey526

    Jersey526 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I'm trying to select the most recent price (ITEM_PRICE) from many in TABLE_REF_PRICES for each of 142K unique products in TABLE_REF_PRODUCTS based upon product id (ID_PRODUCT).

    What I'm getting is each item repeated for the number of prices found in TABLE_REF_PRICES.

    I've done this before but can't remember the syntax (e.g., HAVING, JOIN, etc.)

    Any help woud be appreciated.
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Re: Just The Latest Please!

    Hi, how can we help if you do not provide your table structure and your query that is not giving the correct results?

    Please provide these so someone can help.
     
  3. Jersey526

    Jersey526 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Re: Just The Latest Please!

    Sorry about that. There are two tables involved:

    TABLE_REF_PRODUCTS with two relevant fields:
    - id_product (numeric,12,0)
    - nme_product (varchar2,40)

    TABLE_REF_PRICES with three relevant fields:
    - id_product (numeric,12,0)
    - item_price (numeric,12,2)
    - dte_price (date)

    The code I'm using is as follows:
    Code (SQL):

      SELECT   a.id_product,
               a.nme_product,
               p.dte_price,
               p.item_price
        FROM   table_ref_products a, table_ref_price p
       WHERE   a.id_product = p.id_product
    ORDER BY   a.id_product, p.dte_price
    What I'm getting is a record for each price in the TABLE_REF_PRICE table, for example:

    1111 ITEM A 05/05/2006 556.55
    1111 ITEM A 09/23/2007 570.40
    1111 ITEM A 04/19/2009 610.22
    1111 ITEM A 04/09/2010 604.33
    2222 ITEM B 03/23/2009 299.99
    etc.

    What I'm trying to get is just the most recent price for each item such as the $604.33 for Item A.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Just The Latest Please!

    Code (SQL):
     
    SELECT a.id_product, a.nme_product, p.dte_price, p.item_price
    FROM table_ref_products a, table_ref_price p
    WHERE a.id_product = p.id_product
    AND (a.id_product, p.dte_price) IN (SELECT id_product, MAX(dte_price)
                                                    FROM table_ref_price
                                                    GROUP BY id_product)
    ORDER BY a.id_product, p.dte_price

     
     
  5. Jersey526

    Jersey526 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Re: Just The Latest Please!

    David, thanks for the code which works fine. I was having a brain freeze on "HAVING" and "GROUP BY".