RE: Is it possible in single query?

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Tue, 23 Mar 2004 19:57:55 +0100
Message-ID: <JFEEIGBIDOCCDALDIPLNOEFHCBAA.lex.de.haan@naturaljoin.nl>

Hi Jay,

this looks like a typical case for windowing, with an analytical function. something like the following:

select rank() over (partition by UserID order by UserID,RefDate)

,      UserID,
,      RefDate

from Data;

hope this helps, kind regards,
Lex.

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

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of jaysingh1_at_optonline.net
Sent: dinsdag 23 maart 2004 19:13
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

Received on Tue Mar 23 2004 - 17:39:57 CST