Difference between Row_Number(),Rank(),Dense_Rank() in SQL Server

Introduction

In SQL server, there are mainly three Ranking functions.

In this article I am going to explain the Row_Number(), Rank(), Dense_Rank() and difference between them. When we work on a Result set, Sometimes we required the unique ROWID of each row or a partition of rows.

So we will understand these functions with example and When to use Row_Number(), Rank(), Dense().

Row_Number()

This function will provide the unique ROWID to each row of result set. Let us understand it by example:-

Query:-

     

After the execution we will find the result -

Grade	RowID
One	1
One	2
One	3
Two	4
Two	5
Three	6
Three	7

In the result set RowID is unique for each row.

Rank()

Some times we require to assign a unique value to a set of duplicate rows So this function is used to assign the unique number to each set of duplicate rows in result set or in other words it assigns the unique number to the distinct rows in a result set.

Query:- 

After the execution of above query will be-

Grade	RowID
One	1
One	1
One	1
Two	4
Two	4
Three	6
Three	6

Let us understand, Grade One is repeated three times and starting from 1 So for next group RowID will start from 1+3=4 and Two is repeated two times so next group will start from 4+2=6  and So on.

Dense_Rank()

The only difference between Rank() and Dense_Rank() is that it assigns the unique number to the set of duplicate rows in result set but it provides the next incremented number to the next set of rows contiguously

Query:-

Grade	RowID
One	1
One	1
One	1
Two	2
Two	2
Three	3
Three	3

Let us understand the above result, The Grade One repeated three times and starting from 1 but the next set starts from 2 So it will increase the value on next group instead of next row.