Oracle Analytic Functions: Rank Function
The Oracle/PLSQL RANK function returns the rank of a value in a group of values. It is very similar to the DENSE_RANK function.
However, the 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.
select RANK(2000, 500) WITHIN GROUP (ORDER BY sal, bonus)
Example (as an Analytic Function)
select ename, sal,
RANK() OVER (PARTITION BY dept ORDER BY sal)
where dname = 'Marketing';
The SQL statement above would return all employees who work in the Marketing department and then calculate a rank for each unique salary in the Marketing department.
If two employees had the same salary, the RANK function would return the same rank for both employees.
However, this will cause a gap in the ranks (ie: non-consecutive ranks). This is quite different from the DENSE_RANK function which generates consecutive rankings.