RE: SQL help

From: Clarke, Andrew <andrew.clarke_at_logicacmg.com>
Date: Fri, 25 Aug 2006 11:15:59 +0100
Message-ID: <2BE0472753FAFA4A86DB33C3D02CBB1B1DCD7F@uk-ex002.groupinfra.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Roger Xu Sent: 24 August 2006 20:34
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.

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.

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

Received on Fri Aug 25 2006 - 05:15:59 CDT