RANK function returns the rank of a value in a group of values. It is similar to DENSE_RANK function. 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.
The RANK function can be used two ways – as an Aggregate function or as an Analytic function.
select RANK(10000, 200) WITHIN GROUP (ORDER BY sal,comm) from emp;
The SQL statement above would return the rank of an employee with a salary of $10,000 and a commision of $200 from within the employees table.
Rank Function Syntax #2 – Used As An Analytic Function:
Here RANK function returns the rank of each row of a query with respective to the other rows.
rank() OVER ( [ query_partition_clause] ORDER BY clause )
select ename, deptno,sal, RANK() OVER (PARTITION BY deptno ORDER BY sal) from emp
The SQL statement above would return all employees and calculate a rank for each unique salary in all department. If two employees had the same salary, the RANK function would return the same rank for both employees like shown in below query results. However, this will cause a gap in the ranks (ie: non-consecutive ranks). This is different from the DENSE_RANK function which generates consecutive rankings.
You can watch video tutorial of Rank() Aggregate function here:
You can watch video tutorial of Rank() Analytic function here: