RE: Is it possible in single query?

From: Eberhard, Jeff <Jeff.Eberhard_at_TriumphGS.com>
Date: Tue, 23 Mar 2004 12:18:39 -0700
Message-ID: <86B94CDA99C77A408129A378D2DA8DB80BC79D@compqsrv.rolls-roycegs.com>

SELECT RANK() OVER (PARTITION BY userid ORDER BY refdate) AS user_rank, userid, refdate
FROM table1

-----Original Message-----

From: jaysingh1_at_optonline.net [mailto:jaysingh1_at_optonline.net] Sent: Tuesday, March 23, 2004 11:13 AM
To: oracle-l_at_freelists.org
Subject: Is it possible in single query?

Dear All,

Table Data is like below

UserID RefDate
----- -------------

U1        6/3/03 2:27        
U1        8/9/03 7:47        
U1        3/14/03 7:47        
U1        6/17/03 7:47        

U2        6/2/03 15:34       
U2        9/2/03 15:37       

U3        8/9/02 18:40       
U3        9/9/02 18:45       

U4        10/29/02 18:05     
U4        10/29/02 19:59     
U4        10/29/02 21:23

I need fetch the above data and insert into another temporary reporting table
like below.

Seq# UserID RefDate
---- ----- -------------

1    U1        6/3/03 2:27      
2    U1        8/9/03 7:47      
3    U1        3/14/03 7:47     
4    U1        6/17/03 7:47     

1    U2        6/2/03 15:34     
2    U2        9/2/03 15:37     

1    U3        8/9/02 18:40     
2    U3        9/9/02 18:45     

1    U4        10/29/02 18:05   
2    U4        10/29/02 19:59   
3    U4        10/29/02 21:23

Any help would be really appreciated.
Thanks
Jay

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Tue Mar 23 2004 - 14:01:13 CST