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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Roger Xu Sent: Thursday, August 24, 2006 2:34 PM To: Oracle-L@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.