Re: Is it possible in single query?

  • From: Michael Twaddell <twaddell@xxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 23 Mar 2004 13:13:54 -0600

Jay,

I may be missing something, but I don't think you can get the
results you want based on what is given.  There is no sort
fields that I can see for U1.  That is, I can't create a select
statement that would guarantee the row order of your data as
you show it in the first part of your question.

Now if this is a typo and you really want the result sorted by
userid and refdate, then you could use the following query as
long as you are on 8.1.7 or greater.

insert into temp_table
 select dense_rank() over (partition by userid order by refdate),
        userid,
        refdate
  from original_table;

Michael T.

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2004