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 -----------------------------------------------------------------