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.
- SQL help
- From: Roger Xu
- SQL help