Oracle: RANK() vs. DENSE_RANK() Function
In Oracle SQL, both
DENSE_RANK() are window functions that are used to assign a ranking to each row within a result set based on the specified criteria. However, there is a subtle difference in how they handle ties (i.e., when multiple rows have the same values for the ranking criteria).
The RANK() function assigns a unique rank to each distinct row in the result set. If two or more rows have the same values for the ranking criteria, they receive the same rank, and the next rank is skipped. After that, the subsequent rank will be adjusted accordingly. In other words, there can be gaps in the ranking sequence when ties are encountered.
The DENSE_RANK() function also assigns a unique rank to each distinct row in the result set, like the
RANK() function. However, when there are ties,
DENSE_RANK() does not skip ranks; instead, it assigns the same rank to all tied rows, and the ranking sequence continues uninterrupted. Therefore, there are no gaps in the ranking sequence when using
Let’s illustrate the difference between
DENSE_RANK() with an example:
Consider the following “Employees” table:
SELECT EmployeeName, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank FROM Employees;
As you can see, when using
RANK(), two employees (John and Bob) with a salary of 4000 received the same rank (3), and the next rank (4) was skipped. Then, employees Jane and Mike with a salary of 3500 received the same rank (5).
SELECT EmployeeName, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Dense_Rank FROM Employees;
DENSE_RANK(), when two employees (John and Bob) with a salary of 4000 received the same rank (3), the next rank was not skipped. Instead, the subsequent employee (Jane) with a salary of 3500 received the next rank (4), resulting in no gaps in the ranking sequence.
So, the main difference is that
DENSE_RANK() handles ties without skipping ranks, while
RANK() may skip ranks when ties occur.
- Oracle: TRUNC(date) Function
- MongoDB- INSERT, SELECT, UPDATE, and DELETE Query
- Best Practices to Optimize SQL Query
- Overview, Editions, and Features of Oracle Database
- MongoDB- ADD, MODIFY/RENAME, or DELETE Key