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!

Getting latest records

Discussion in 'SQL PL/SQL' started by pshuttleworth, May 28, 2013.

  1. pshuttleworth

    pshuttleworth Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi

    I need to get the latest cost prices for different products where the product has multiple cost proces over time. Each time the cost price changes it gets a new sequence number im using the query below which currently gives all records so i get multiple cost prices. So i need the latest record either from the sequence number or date.

    select prd_for SKU,
    qty,
    short_descr,
    dc_dsptchs.ext_ref LOAD,
    mmgs.mmg5_id DEPT,
    prd_sup_us.ltst_cost_prc,
    prd_sup_us.cost_prc_eftv_from,
    prd_sup_us.seq
    from retail.dc_dsptch_items,
    retail.dc_dsptchs,
    retail.retl_prds,
    retail.mmgs,
    retail.prd_sup_us
    where ext_ref = '&Load'
    and dc_dsptchs.art_dd_id = dc_dsptch_items.dd_art_dd_id
    and retl_prds.rp_id = dc_dsptch_items.prd_for
    and retl_prds.mmg_art_mmg_id = mmgs.mmg_art_mmg_id
    and prd_sup_us.rp_rp_id = retl_prds.rp_id

    Current results look like this:

    SKU QTY SHORT_DESCR LOAD DEPT LTST_COST_PRC COST_PRC_EFTV_FROM SEQ
    036121061 10 HK 3PK WHITE PLAIN W 605607 1016 1.35 25/09/2006 2
    036121061 10 HK 3PK WHITE PLAIN W 605607 1016 1.73 22/05/2013 3
    036121061 10 HK 3PK WHITE PLAIN W 605607 1016 1.73 18/01/2013 1
    036121076 10 HK 3PK BLUE CHECK Bl 605607 1016 1.35 25/09/2006 2
    036121076 10 HK 3PK BLUE CHECK Bl 605607 1016 1.73 22/05/2013 3
    036121076 10 HK 3PK BLUE CHECK Bl 605607 1016 1.73 18/01/2013 1
    036122152 10 HK 7 PK WHITE White 605607 1016 2.00 13/06/2006 2
    036122152 10 HK 7 PK WHITE White 605607 1016 2.71 22/05/2013 3
    036122152 10 HK 7 PK WHITE White 605607 1016 2.71 03/04/2013 1

    thanks

    Paul
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Is SKU the sequence Number? If it is then please specify in which table this sequence number field resides? By that we can provide condition to restrict it.
     
  3. pshuttleworth

    pshuttleworth Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    hi

    The sequence number is in the number in prd_sup_us.seq. SKU is the product code of the item

    Thanks

    Paul
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Then add the following condition line in the where clause.

    and prd_sup_us.seq = (select max(seq) from retail.prd_sup_us where rp_rp_id = retl_prds.rp_id);
     
  5. pshuttleworth

    pshuttleworth Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    hi

    That worked, many thanks for your help.

    Easy when you know how

    Paul
     
  6. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Yes, Practice makes Man Perfect..