Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    9

    Unanswered: Select Distinct Record

    HERE IS MY DATA

    PART PRICE DATE
    1 , 1 , 1-30-05
    1 , 1 , 1-14-05
    1 , 2 , 1-22-05
    1 , 2 , 1-26-05


    when i try to pull only the record for the latest date, since there is 2 prices it brings in 2 records since i am using the MAX function on the date but grouping distiguishes price of 1 and price of 2 separately. can anyone help with how to tell it i only want the latest record for part #1 no matter the price but display the price with that record? thanks GRT

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    This is simple SQL, did you try using the MAX function?:
    Code:
    SELECT * FROM MYDATA P
     WHERE PART=1
         AND PRICE_DATE = (
    SELECT MAX(PRICE_DATE) FROM MYDATA D
     WHERE D.PART = P.PART);
    Also, i recommend you read this or this or this.

    Last edited by LKBrwn_DBA; 03-10-05 at 16:07.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2004
    Posts
    9
    yes i tried the Max function but the data i supplied is just an example i am working with over 3000 sku's with a total records count exceeding 250k. so the option to select based on a part number is not there ans so the group by function required with a max statement does not work
    here is the actual statement

    select
    ii.catalog_no CAT,
    ih.name NAME,
    II.sale_unit_price PRICE,
    max(ih.invoice_date) iDATE
    from ifsapp.customer_order_inv_item ii,
    ifsapp.customer_order_inv_head ih
    where
    ifsapp.ii.invoice_id = ih.invoice_id
    GROUP BY Ii.catalog_no,
    ih.name,
    II.sale_unit_price,
    ih.invoice_date
    ORDER BY IH.INVOICE_DATE DESC

    the use of a Max statement requires a group by statement which segregates also by customer name and the different prices so i get multiple records not just the final record.....

    thanks GRT
    Last edited by GRTHIGPEN; 03-10-05 at 16:18.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select ii.catalog_no        CAT
         , ih.name              NAME
         , ii.sale_unit_price   PRICE
         , ih.invoice_date      iDATE
      from ifsapp.customer_order_inv_item ii
    inner
      join ifsapp.customer_order_inv_head ih
        on ii.invoice_id = ih.invoice_id
    inner
      join ifsapp.customer_order_inv_head ih2
        on ii.invoice_id = ih2.invoice_id
    group 
        by ii.catalog_no
         , ih.name
         , ii.sale_unit_price
         , ih.invoice_date
    having ih.invoice_date
         = max(ih2.invoice_date)     
    order 
        by ih.invoice_date desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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