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
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@xxxxxxxxxxxx> Sent: Mar 23, 2004 11:13 AM To: oracle-l@xxxxxxxxxxxxx Cc: jaysingh1@xxxxxxxxxxxxx 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@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 -----------------------------------------------------------------
---------------------------------------------------------------- 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 -----------------------------------------------------------------
