RE: Is it possible in single query?

From: david wendelken <davewendelken_at_earthlink.net>
Date: Tue, 23 Mar 2004 14:33:21 -0800 (PST)
Message-ID: <2829003.1080081201688.JavaMail.root@ernie.psp.pas.earthlink.net>

It is unclear whether he wants dense_rank or rank.

Let's say the same userid has 4 records, with the 2nd and 3rd records having the same refdate value.

Dense_rank will order them 1,2,3,4.
Rank will order them 1,2,2,3.

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

From: "Eberhard, Jeff" <Jeff.Eberhard_at_TriumphGS.com> Sent: Mar 23, 2004 11:18 AM
To: "'oracle-l_at_freelists.org'" <oracle-l_at_freelists.org> Cc: "'jaysingh1_at_optonline.net'" <jaysingh1_at_optonline.net> Subject: RE: Is it possible in single query?

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

---

---

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 - 18:24:31 CST