What is dense_rank function and it's usage ?

Sangeeta Shukla

• Feb 19th, 2007

 

A dense_rank fn is similar to Rank fn except for a difference that it does not skip sequential ranking numbers. If two values are the same and have same rank for ex. 3, then in dense_rank fn the next non duplicate value will be ranked 4. In normal Rank fn, the next non duplicate value  would have been ranked 5 .

g_sidhu

 Profile 

• Feb 5th, 2008

 

Rank Function can cause non-consecutive rankings if the tested values are the same. Whereas, the dense_rank function will always result in consecutive rankings.

Teju_hassan

 Profile Answers by Teju_hassan 

• Apr 20th, 2011

 

See the below example to understand better.

select dense_rank,rank,sal
from emps
order by sal desc

dense_rank|rank|sal
1                   1       10000
2                    2        5000
2                    2        5000
4                    3        4000

In the above example you can see that in the dense_rank function if there is a tie then the next position will be skipped and in the rank function the count start from the next position.