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 17:54:14 -0600
Message-ID: <4060CE26.9080006@raytheon.com>


David,

Correct. I'm assuming Jay wanted his results to look like

Seq# UserID RefDate
---- ----- -------------

1       U1        3/14/03 7:47
2       U1         6/3/03 2:27
3       U1        6/17/03 7:47
4       U1         8/9/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

If not, then I'm not sure how to accomplish what he wants.

Michael T.

david wendelken wrote:

> Michael,
> 
> I assume the typo you refer to under U1 is that the dates are not ordered from first to last, or last to first, but are in a jumbled-up order.
> 
> David
> 
> -----Original Message-----
> From: Michael Twaddell <twaddell_at_raytheon.com>
> Sent: Mar 23, 2004 11:13 AM
> To: oracle-l_at_freelists.org
> Cc: jaysingh1_at_optonline.net
> Subject: Re: Is it possible in single query?
> 
> 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
> -----------------------------------------------------------------
> 
> ----------------------------------------------------------------
> 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 - 19:17:22 CST

Original text of this message

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