| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Is it possible in single query?
Actually
dense_rank() will order them 1, 2, 2, 3
and
rank() will order them 1, 2, 2, 4
If the seq# field can't repeat within a group, he will need to use the row_number() function.
regards,
Michael T.
david wendelken wrote:
> It is unclear whether he wants dense_rank or rank.
>
> Let's say the same userid has 4 records, with the 2nd and 3rd records having the same refdate value.
>
> Dense_rank will order them 1,2,3,4.
> Rank will order them 1,2,2,3.
>
>
> -----Original Message-----
> From: "Eberhard, Jeff" <Jeff.Eberhard_at_TriumphGS.com>
> Sent: Mar 23, 2004 11:18 AM
> To: "'oracle-l_at_freelists.org'" <oracle-l_at_freelists.org>
> Cc: "'jaysingh1_at_optonline.net'" <jaysingh1_at_optonline.net>
> Subject: RE: Is it possible in single query?
>
> SELECT RANK() OVER (PARTITION BY userid ORDER BY refdate) AS user_rank,
> userid, refdate
> FROM table1
>
> -----Original Message-----
> From: jaysingh1_at_optonline.net [mailto:jaysingh1_at_optonline.net]
> Sent: Tuesday, March 23, 2004 11:13 AM
> To: oracle-l_at_freelists.org
> Subject: Is it possible in single query?
>
> 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
> -----------------------------------------------------------------
>
-- 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:18:37 CST
 |
 |