Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Difference between ROW_NUMBER and RANK

Vivek LApr 28 2011 — edited Apr 28 2011
Hi,

Just want to know, is there any other signifacnt difference in behaviour of RANK and ROW_NUMBER other then in an event of a tie?
As far as I know, RANK rates two rows as eqaul in an event of tie, whereas ROW_NUMBER treats them as different with unique running serial number provided to each record.

here is some sample code that I worked upon :

For RANK : 
WITH T AS (
 SELECT 'EMP1' NAME, 1000 SAL FROM DUAL
 UNION
 SELECT 'EMP2', 200 FROM DUAL
 UNION 
 SELECT 'EMP3', 500 FROM DUAL
 UNION
 SELECT 'EMP4', 1000 FROM DUAL

)
SELECT NAME,
       SAL,
       RANK() OVER (ORDER BY SAL DESC ) "rank"
FROM   T
ORDER BY SAL ;
Output went like :
NAME SAL rank 
EMP2 200 4 
EMP3 500 3 
EMP1 1000 1 
EMP4 1000 1
whereas with
ROW_NUMBER
WITH T AS (
 SELECT 'EMP1' NAME, 1000 SAL FROM DUAL
 UNION
 SELECT 'EMP2', 200 FROM DUAL
 UNION 
 SELECT 'EMP3', 500 FROM DUAL
 UNION
 SELECT 'EMP4', 1000 FROM DUAL

)
SELECT NAME,
       SAL,
       ROW_NUMBER() OVER (ORDER BY SAL DESC ) "rank"
FROM   T
ORDER BY SAL;
outout comes as 
NAME SAL rank 
EMP2 200 4 
EMP3 500 3 
EMP1 1000 1 
EMP4 1000 2
This post has been answered by 780914 on Apr 28 2011
Jump to Answer

Comments

780914
Hi,

RANK is the function to rank records based on a key column, while row_number is to give a unique number to a row irrespective of any column value. Check the below query and output to get better distinction between RANK, DENSE_RANK and ROW_NUMBER.
WITH T AS (
 SELECT 'EMP1' NAME, 1000 SAL FROM DUAL
 UNION
 SELECT 'EMP2', 200 FROM DUAL
 UNION 
 SELECT 'EMP3', 500 FROM DUAL
 UNION
 SELECT 'EMP4', 1000 FROM DUAL
 
)
SELECT NAME,
       SAL,
       RANK() OVER (ORDER BY SAL DESC ) "rank",
       DENSE_RANK() OVER (ORDER BY SAL DESC ) "DENSE_rank",
       ROW_NUMBER() OVER (ORDER BY SAL DESC ) "ROW_NUMBER"
FROM   T;



NAME        SAL       rank DENSE_rank ROW_NUMBER
---- ---------- ---------- ---------- ----------
EMP1       1000          1          1          1
EMP4       1000          1          1          2
EMP3        500          3          2          3
EMP2        200          4          3          4
1 person found this helpful
Vivek L
Thanks SBH, but if row_number does not depend on any column value, then why do we need a Window Clause?? woudn't it work same as ROWNUM then?

also, how does ROW_NUMBER decide to give serial number to different rows in an even of tie?

In the given example 
EMP1       1000          1          1          1
EMP4       1000          1          1          2
why EMP1 is given ROW_NUMBER as 1 and EMP4 as 2?
780914
Answer
Yeah, with row_number, i meant to say that it would not describe any analytic property of a column but would generate unique number for complete record. Windowing clause would just serve as the basis of numbering.

ROW_NUMBER ignores the event of tie, but numbers the rows in the output.

"why EMP1 is given ROW_NUMBER as 1 and EMP4 as 2? "
Because numbering was done for the ranked records. Check the below query, rank function is commented and output gives the correct numbering
WITH T AS (
 SELECT 'EMP1' NAME, 1000 SAL FROM DUAL
 UNION
 SELECT 'EMP2', 200 FROM DUAL
 UNION 
 SELECT 'EMP3', 500 FROM DUAL
 UNION
 SELECT 'EMP4', 1000 FROM DUAL
 
)
SELECT NAME,
       SAL,
       --RANK() OVER (ORDER BY sal DESC ) "rank",
       --DENSE_RANK() OVER (ORDER BY SAL DESC ) "DENSE_rank",
       ROW_NUMBER() OVER (ORDER BY 1) "ROW_NUMBER"
FROM   T;


NAME        SAL ROW_NUMBER
---- ---------- ----------
EMP1       1000          1
EMP2        200          2
EMP3        500          3
EMP4       1000          4
Marked as Answer by Vivek L · Sep 27 2020
Vivek L
first two lines of your response cleared my doubts.. thanks SBH
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 26 2011
Added on Apr 28 2011
4 comments
20,027 views