RE: SQL help
- From: "Clarke, Andrew" <andrew.clarke@xxxxxxxxxxxxx>
- To: <Roger.Xu@xxxxxxxxxxx>, "Oracle-L@Freelists. Org \(E-mail\)" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 25 Aug 2006 11:15:59 +0100
Can you say "Analytics rock"?
select CUSTOMER_NUMBER,INVOICEDATE
from ( select CUSTOMER_NUMBER
,INVOICEDATE
, rank () over (partition by CUSTOMER_NUMBER order by
INVOICEDATE desc) as inv_date_rank
from order_history )
where inv_date_rank <= 3
order by CUSTOMER_NUMBER,INVOICEDATE
/
You want to try DENSE_RANK or ROW_NUMBER() instead of RANK() and use the
one that fits your needs best.
Cheers, APC
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Roger Xu
Sent: 24 August 2006 20:34
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.
This e-mail and any attachment is for authorised use by the intended
recipient(s) only. It may contain proprietary material, confidential
information and/or be subject to legal privilege. It should not be copied,
disclosed to, retained or used by, any other party. If you are not an intended
recipient then please promptly delete this e-mail and any attachment and all
copies and inform the sender. Thank you.
Other related posts:
