Re: Is it possible in single query?

From: david wendelken <davewendelken_at_earthlink.net>
Date: Tue, 23 Mar 2004 14:29:22 -0800 (PST)
Message-ID: <22057275.1080080962201.JavaMail.root@ernie.psp.pas.earthlink.net>

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

Received on Tue Mar 23 2004 - 18:25:53 CST