RE: Is it possible in single query?

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Tue, 23 Mar 2004 15:48:02 -0500
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CFECFC56@bosmail00.bos.il.pqe>

Something like this ought to do it:
create table test_user(userid varchar2(2), refdate date);

insert into test_user values('U1',to_date('6/3/03 2:27','mm/dd/yy hh24:mi'));
insert into test_user values('U1',to_date('8/9/03 7:47','mm/dd/yy hh24:mi'));
insert into test_user values('U1',to_date('3/14/03 7:47','mm/dd/yy hh24:mi'));
insert into test_user values('U1',to_date('6/17/03 7:47','mm/dd/yy hh24:mi'));
insert into test_user values('U2',to_date('6/2/03 15:34','mm/dd/yy hh24:mi'));
insert into test_user values('U2',to_date('9/2/03 15:37','mm/dd/yy hh24:mi'));
insert into test_user values('U3',to_date('8/9/02 18:40','mm/dd/yy hh24:mi'));
insert into test_user values('U3',to_date('9/9/02 18:45','mm/dd/yy hh24:mi'));
insert into test_user values('U4',to_date('10/29/02 18:05','mm/dd/yy hh24:mi'));
insert into test_user values('U4',to_date('10/29/02 19:59','mm/dd/yy hh24:mi'));
insert into test_user values('U4',to_date('10/29/02 21:23','mm/dd/yy hh24:mi'));

commit;
select row_number() over (partition by userid order by refdate) SEQ#,

       userid,
       refdate

from test_user;
1* select row_number() over (partition by userid order by refdate) SEQ#,userid , to_char(refdate,'mm/dd/yy hh24:mi') from test_user SQL> / SEQ# US TO_CHAR(REFDAT
---------- -- --------------

         1 U1 03/14/03 07:47
         2 U1 06/03/03 02:27
         3 U1 06/17/03 07:47
         4 U1 08/09/03 07:47
         1 U2 06/02/03 15:34
         2 U2 09/02/03 15:37
         1 U3 08/09/02 18:40
         2 U3 09/09/02 18:45
         1 U4 10/29/02 18:05
         2 U4 10/29/02 19:59
         3 U4 10/29/02 21:23

11 rows selected.

SQL> -----Original Message-----
From: Daniel Fink [mailto:Daniel.Fink_at_Sun.COM] Sent: Tuesday, March 23, 2004 2:30 PM
To: oracle-l_at_freelists.org
Subject: Re: Is it possible in single query?

Jay,

My brain's a little befuddled with another problem right now, so I can't give you a hard and fast answer, but I think if you look at the analytical functions (RANK, et.al.) you should be able to come up with your answer.

Daniel

jaysingh1_at_optonline.net wrote:
> 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 - 16:01:27 CST