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.
- There must be the same number of expressions in the first expression list as we have to provide in the ORDER BY clause.
- The expression lists match by position so the data types must be compatible between the expressions in the first expression list as in the ORDER BY clause.i.e order by clause data type ,expression type data types must be compatible
Example:
Let's look at some Oracle RANK function examples and explore how to use the RANK function in Oracle/PLSQL.
For example:
select RANK(2000, 500) WITHIN GROUP (ORDER BY sal, bonus)
from emp;
Example (as an Analytic Function)
select ename, sal,
RANK() OVER (PARTITION BY dept ORDER BY sal)
from emp
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.
