Rank and Dense_Rank are window functions. In SQL the syntax for both functions are almost same.
In this article, will discuss about RANK, DENSE_RANK and how to use it with an example.
RANK: it gives you the ranking within your ordered partition.
Syntax: RANK() OVER (partition by DepartmentId order by salary desc) as rank
let’s understand ordered partition with an example.
We have table which looks like this. The question is to find employees who have the highest salary in each of the departments.
Now, if i partition by Department id result will be
But if you notice, partitions are not sorted in any order so we need so sort it by some column. Here, we will sort it by salary because we want to find highest salary for each Department.
Now, will use rank function to assign rank to each row.
Notice, rank function assigned same rank to Employees who had same Salary and rank 2 has been skipped. So rank function actually assigns same rank if there is a tie and it also skips next ranking.
To slow the result you can filter it by rank (where rank=1).
Finally, below is the SQL
select
Department,
Employee,
salary,
rankfrom (
select
DepartmentId as Department,
name as Employee,
salary,
RANK() OVER (partition by DepartmentId order by salary desc) as rank
from Employee)tempwhere temp.rank=1;
How does dense_rank work ?
No ranks are skipped if there are ranks with multiple items.
