RE: SQL help

From: Ken Naim <kennaim_at_gmail.com>
Date: Thu, 24 Aug 2006 15:04:09 -0500
Message-ID: <01bd01c6c7b8$76873480$b4ae6a44@KenHome>

Select * from (Select customer_number, invoicedate, row_number() over (partition by customer_number, order by invoice_date desc) rn from order_history) where rn =3. if you have more than 3 invoice per day you need to add something to the order by so you get the same 3 records each time you run it, or you need to replace row_number with rank or dense_rank depending on your exact needs.  

Ken Naim  

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Roger Xu
Sent: Thursday, August 24, 2006 2:34 PM
To: Oracle-L_at_Freelists. Org (E-mail)
Subject: SQL help  

SQL> select CUSTOMER_NUMBER,INVOICEDATE
  2 from order_history
  3 order by CUSTOMER_NUMBER,INVOICEDATE;  

CUSTOMER_N INVOICEDA
---------- ---------

1137065    10-AUG-06
1137065    10-AUG-06
1137065    17-AUG-06
1137065    17-AUG-06
1137065    18-AUG-06
1137065    18-AUG-06
1137065    21-AUG-06
1137065    22-AUG-06
1137599    19-AUG-06
1137599    19-AUG-06
1137599    20-AUG-06
1137599    20-AUG-06
1137599    22-AUG-06
1137599    22-AUG-06
1137599    23-AUG-06
1137599    23-AUG-06
 

16 rows selected.  

How do I modify this SQL to find out the last 3 invoiceDATE for each customer without using PL/SQL?  

CUSTOMER_N INVOICEDA
---------- ---------

1137065    18-AUG-06
1137065    21-AUG-06
1137065    22-AUG-06
1137599    22-AUG-06
1137599    23-AUG-06
1137599    23-AUG-06
 

Thanks,  

Roger

This e-mail is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Any review, dissemination, copying, printing or other use o

---

This email has been scanned for all viruses by the MessageLabs Email Security System.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 24 2006 - 15:04:09 CDT