Rank Function
Rank() and Dense_Rank() functions
Introduced in SQL Server 2005
Returns a rank starting at 1 based on the ordering of rows imposed by the ORDER BY clause
ORDER BY clause is required
PARTITION BY clause is optional
When the data is partitioned, rank is reset to 1 when the partition changes
RANK is a temporary value calulated when the query is run.
Syntax
RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )
Arguments
OVER ( [ partition_by_clause ] order_by_clause)
Return Types
bigint
Difference between Rank and Dense_Rank functions
RANK gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5.
DENSE_RANK again gives you the ranking within your ordered partition, but the ranks are consecutive. No ranks are skipped if there are ranks with multiple items.
Example :
Id | Name | Gender | Salary |
1 | Aark | Male | 8000 |
2 | Micky | Male | 8000 |
3 | Mini | Female | 5000 |
4 | Sara | Female | 4000 |
5 | Goffy | Male | 3500 |
6 | Mary | Female | 6000 |
7 | Donald | Male | 6500 |
8 | Jodi | Female | 4500 |
9 | Tommy | Male | 7000 |
10 | Ronald | Male | 6800 |
Create Table Employees( Id int primary key, Name nvarchar(50), Gender nvarchar(10), Salary int ) Go
Insert Into Employees Values (1, 'Aark', 'Male', 8000) Insert Into Employees Values (2, 'Micky', 'Male', 8000) Insert Into Employees Values (3, 'Mini', 'Female', 5000) Insert Into Employees Values (4, 'Sara', 'Female', 4000) Insert Into Employees Values (5, 'Goffy', 'Male', 3500) Insert Into Employees Values (6, 'Mary', 'Female', 6000) Insert Into Employees Values (7, 'Donald', 'Male', 6500) Insert Into Employees Values (8, 'Jodi', 'Female', 4500) Insert Into Employees Values (9, 'Tommy', 'Male', 7000) Insert Into Employees Values (10, 'Ronald', 'Male', 6800) Go
Without PARTITION BY clause
SELECT Id , Name, Salary, Gender, RANK() OVER (ORDER BY Salary DESC) AS [Rank], DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank FROM Employees
Id | Name | Gender | Salary | Rank | DenseRank |
1 | Aark | Male | 8000 | 1 | 1 |
2 | Micky | Male | 8000 | 1 | 1 |
9 | Tommy | Male | 7000 | 3 | 2 |
10 | Ronald | Male | 6800 | 4 | 3 |
7 | Donald | Male | 6500 | 5 | 4 |
6 | Mary | Female | 6000 | 6 | 5 |
3 | Mini | Female | 5000 | 7 | 6 |
8 | Jodi | Female | 4500 | 8 | 7 |
4 | Sara | Female | 4000 | 9 | 8 |
5 | Goffy | Male | 3500 | 10 | 9 |
With PARTITION BY clause
SELECT Id , Name, Salary, Gender, RANK() OVER (PARTITION BY Gender ORDER BY Salary DESC) AS [Rank], DENSE_RANK() OVER (PARTITION BY Gender ORDER BY Salary DESC) AS DenseRank FROM Employees
Id | Name | Gender | Salary | Rank | DenseRank |
6 | Mary | Female | 6000 | 1 | 1 |
3 | Mini | Female | 5000 | 2 | 2 |
8 | Jodi | Female | 4500 | 3 | 3 |
4 | Sara | Female | 4000 | 4 | 4 |
1 | Aark | Male | 8000 | 1 | 1 |
2 | Micky | Male | 8000 | 1 | 1 |
9 | Tommy | Male | 7000 | 3 | 2 |
10 | Ronald | Male | 6800 | 4 | 3 |
7 | Donald | Male | 6500 | 5 | 4 |
5 | Goffy | Male | 3500 | 6 | 5 |