Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Is it possible in single query?

Re: Is it possible in single query?

From: Michael Twaddell <twaddell_at_raytheon.com>
Date: Tue, 23 Mar 2004 13:13:54 -0600
Message-ID: <40608C72.4000103@raytheon.com>


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_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
-----------------------------------------------------------------
Received on Tue Mar 23 2004 - 13:42:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US