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

References

Leave a Reply

Your email address will not be published. Required fields are marked *