| Sapna |
Posted: Jan 20, 2006 06:17:49 AM |
Total Post: 2
Joined: Jan, 2006
|
Hi,
I have been given a condtion for which I need to form a query.
Consider a table Cust_Ord as below...
Cust_ID Order_Date
-------- -------------
5 7/14/2005
2 7/14/2005
4 5/15/2005
5 7/12/2005
5 5/14/2005
5 8/14/2005
4 9/14/2005
4 9/18/2005
4 4/19/2005
4 10/20/2005
2 11/9/2005
2 10/14/2005
I need to form a query to list the cust_Id and the latest 2(can vary) orders of each of the customer..the result being as below...
Cust_ID Order_Date
-------- -------------
5 8/14/2005
5 7/14/2005
4 10/20/2005
4 9/18/2005
2 11/9/2005
2 10/14/2005
Please provide ur feedback.
Thanks in advance! |
|
|
 Vigyan |
| Posted: Jan 20, 2006 08:59:57 AM |  |
|
Total Post: 386
Joined: May, 2001
|
How is that if you put distinct in the query?
select distinct Cust_ID, Order_Date from table.
Vigyan
|
|
|
|
|
| Sapna |
| Posted: Jan 22, 2006 11:38:26 PM | |
|
Total Post: 2
Joined: Jan, 2006
|
Hi,
Distinct will not work as it would list all the records as the CustId and order_date combination is never repeated. And I need the latest 2 orders for every Cust_Id
|
|
|
|
|
| Devesh |
| Posted: Feb 23, 2006 01:16:29 AM | |
|
Total Post: 29
Joined: Oct, 2005
|
I think this will resolve u r prob.
SELECT * FROM CO;
CID ODATE
----- ---------
5 14-JUL-05
2 14-JUL-05
4 15-MAY-05
5 12-JUL-05
5 14-MAY-05
5 14-AUG-05
4 14-SEP-05
4 18-SEP-05
4 19-APR-05
4 20-OCT-05
2 09-NOV-05
SELECT * FROM ( SELECT CID,ODATE,RANK()
OVER (PARTITION BY CID ORDER BY ODATE DESC) TOP2 FROM CO)
WHERE TOP2 <=2;
CID ODATE TOP2
----- --------- ----------
2 09-NOV-05 1
2 14-OCT-05 2
4 20-OCT-05 1
4 18-SEP-05 2
5 14-AUG-05 1
5 14-JUL-05 2
|
|
|
|
|
| Manish |
| Posted: Mar 10, 2006 05:22:30 AM | |
|
Total Post: 2
Joined: Mar, 2006
|
SQL> select cno, odate from (select cno, odate, row_number()
2 over (partition by cno order by odate desc) as rno from cust)
3 where rno < 3 order by 1 desc, 2 desc;
CNO ODATE
---------- ---------
5 14-AUG-05
5 14-JUL-05
4 20-OCT-05
4 18-SEP-05
2 09-NOV-05
2 14-OCT-05
6 rows selected.
|
|
|
|
|
| Manu |
| Posted: May 20, 2006 08:01:35 AM | |
|
Total Post: 11
Joined: May, 2006
|
SELECT * FROM CUST_ORD A WHERE
2>= (SELECT COUNT(DISTINCT ODATE) FROM CUST_ORD B WHERE
A.CID = B.CID AND A.ODATE <= B.ODATE);
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
