Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006

    Question Unanswered: eliminating duplicates

    hai ,

    i have 3 tables namely customer(cust_id,cust_name), payers(cust_id,invoice_date) , orders(cust_id,order_date) now i want to select only those customers who have order_date or invoice_date within the last 365 days from sysdate,also if i have duplicate customers with the same cust_id,cust_name who have different order_date and invoice_date and i want to eliminate the duplicate customers by selecting only distinct customers by selecting the latest order_Date or invoice_date. i am using the following query to do that, but i am unable eliminate the duplicates even while using group by.
    can anyone help me with this.

    select cust.cust_id,cust.cust_name,rec.order_date,pay.inv oice_date from customers cust,
    recievers rec,payers pay where
    cust.cust_id=rec.cust_id and cust.cust_id=pay.cust_id
    and ((round(sysdate-rec.order_date)<365)or

    thank you

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    Without too much philosophy, would SELECT DISTINCT ... do any good?

    [EDIT] Added MAX date

    You could restrict the output (regarding the maximum date value) using such a subquery:
       AND rec.order_date = (SELECT MAX (rec1.order_date)
                               FROM receivers rec1
                              WHERE rec1.cust_id = rec.cust_id)
    Last edited by Littlefoot; 01-15-06 at 03:41.

  3. #3
    Join Date
    Jan 2006


    thanks little foot it helped a lot

Posting Permissions

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