RE: SQL help

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.

Other related posts: