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 :

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

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

References

• https://msdn.microsoft.com/en-us/library/ms189798.aspx
• http://csharp-video-tutorials.blogspot.in/2015/10/rank-and-denserank-in-sql-server.html