We read on applications of analytical function, ROW_NUMBER, in a prior blog. We will now focus on related analytical functions called RANK and DENSE_RANK and its differences with each other and ROW_NUMBER.
Assume there is an existing emp table with the following data.
For each department, we want to retrieve salaries (sal) in descending order along with values for RANK, DENSE_RANK and ROW_NUMBER. After reading the prior blog, it would be reasonable to assume ROW_NUMBER would return a value of 1 for the highest salary in each department. RANK and DENSE_RANK would also function in the same manner.
The differences however emerge when, in a given department, salaries are same for two or more employees. As shown in the illustration below, two employees ( empno ) 7788 and 7902 earn $3000 each which is the highest salary in department ( deptno ) 20. Hence, both DENSE_RANK and RANK provide a value of 1. For the employee earning the next highest salary (7566) , DENSE_RANK displays 2. RANK actually skips 2 to display 3 because there were 2 prior employess ( 7788, 7902 ) earning the higest salary in department 20.
ROW_NUMBER, on the other hand, has no consideration at all for multiple employees earning same salary. It provides sequential numbers from one row to the other.
The actual code that produces these results is provided below.
SELECT empno, deptno, sal,
DENSE_RANK() OVER (PARTITION BY
deptno ORDER BY sal DESC NULLS
RANK() OVER (PARTITION BY deptno
ORDER BY sal DESC NULLS LAST) RANK,
ROW_NUMBER() OVER (PARTITION BY deptno
ORDER BY sal DESC NULLS LAST) ROW_NUMBER
WHERE deptno IN (10, 20)
ORDER BY DEPTNO, RANK;