RE: Is it possible in single query?
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 23 Mar 2004 15:48:02 -0500
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@xxxxxxx]
Sent: Tuesday, March 23, 2004 2:30 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx 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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
