Difference between rank and dense_rank Oracle

What is the difference between RANK and DENSE_RANK functions in Oracle ?

In simple words both does the same except RANK skips if there is a tie and DENSE_RANK does not. So remember to use DENSE_RANK to use in Nth largest queries.

Detailed explanation of RANK and DESNSE_RANK given in another posts

RANK Function

DENSE_RANK Function

Example :-

Consider below emp table data

EMPNO EMPNAME SALARY DEPTNO
10 Bill 12000 5
11 Solomon 10000 5
12 Susan 10000 5
13 Wendy 9000 1
14 Benjamin 7500 1
15 Tom 7600 1
16 Henry 8500 2
17 Robert 9500 2
18 Paul 7700 2

select empno,salary,
rank() over(order by salary asc) “rank”,
dense_rank() over(order by salary asc) “dense_rank”
from emp;

See the result

Difference between RANK and DENSE_RANK
Difference between RANK and DENSE_RANK

RANK function skips number 8 while DENSE_RANK does not.

Advertisements

2 thoughts on “Difference between rank and dense_rank Oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s