Uses of Row_number, Rank and Dense Rank in SQL Server

This blog post explains the various uses of Row_number, Rank and Dense Rank in SQL Server

Introduction

 

In this article, we will discuss how to use the ranking functions in SQL Server. Please create the sample student mark table and insert the records as given below:

Create Table StudentMark
(
StudentId Bigint Identity(1,1),
StudentName Varchar(50),
SubMarks Numeric(18,1)
)
Insert Into StudentMark values ('raj',99)
Insert Into StudentMark values ('ram',90)
Insert Into StudentMark values ('baskar',85)
Insert Into StudentMark values ('naveen',90)
Insert Into StudentMark values ('kumar',98)
Insert Into StudentMark values ('arul',99)
Insert Into StudentMark values ('tharun',95)
Insert Into StudentMark values ('karthi',91)
Insert Into StudentMark values ('anand',90)
Select * from StudentMark

ROW_NUMBER()

 

The ROW_NUMBER() ranking function returns a unique sequential number for each row within the partition of the specified, starting at 1 for the first row in each partition and without repeating or skipping numbers in the ranking result of each partition. If there are duplicate values within the row set, the ranking ID numbers will be assigned arbitrarily.

Select *,ROW_NUMBER() Over(Order by SubMarks desc) as RowNumber From StudentMark

 

 

If the PARTITION BY clause is specified, the ranking row number will be reset for each partition. In the previously created table, the query below shows how to use the ROW_NUMBER ranking function to rank the StudentMark table rows according to the marks of each student.

Select *,ROW_NUMBER() Over(Partition by SubMarks Order by SubMarks) as RowNumber From StudentMark

 

 

RANK()

 

The RANK() ranking function returns a unique rank number for each distinct row to a specified column value, starting at 1 for the first row in each partition, with the same rank for duplicate values and leaving gaps between the ranks, this gap appears in the sequence after the duplicate values.

Select *,RANK() Over(Order by SubMarks Desc) as 'Rank' From StudentMark

 

 

DENSE_RANK()

 

The DENSE_RANK() ranking function is similar to the RANK() function by generating a unique rank number for each distinct row to a specified column value, starting at 1 for the first row in each partition, ranking the rows with equal values with the same rank number. However, it doesn't skip any rank, therefore leaving no gaps between ranks.

Select *,DENSE_RANK() Over(Order by SubMarks Desc) as 'Dense Rank' From StudentMark

 

 

Summary

 

In this article, we learned about Row_Number() ,Rank(), and Dense_Rank() in SQL Server.