Difference Between Row_Number() Rank() And Dense_Rank() In SQL Server

Atul Rawat
Sep 28, 2015
71.9k
0
4
Article

Introduction

In this article we will learn about some SQL functions Row_Number() ,Rank(), and Dense_Rank() and the difference between them.

Creating a table in SQL Server

Here I have an Employe table, the following is the sample data for the Employe Table.

ROW_NUMBER() Function without Partition By clause

Row_number plays a very important role in SQL server. Row_Number function can help to perform more complex ordering of row in the report format than allow the over clause in SQL standard.

Syntax

ROW_NUMBER () OVER ([PARTITION BY value_exp, ... [ n ]] ORDER BY_clause)

Here we will play with the above Employe table. Let's see how we can use Row_Number() function.

Over specified the order of the row and Order by sort order for the record. By default order by sort in ascending order.

Example

select *, ROW_NUMBER() over(order by EmpName) as rownumber from Employe

The following is the output of the above query.

ROW_NUMBER() Function with Partition By clause

If we want to add row number to each group, and it is reset for every group, let's take a look at the Employe table

Example

Select *, ROW_NUMBER() over(partition by Empname order by Empname ) as rownumber from Employe

The following is the OUTPUT of above query

Rank() Function

This function will assign a unique value to each distinct Row, but it leaves a group between the groups.

Example

SELECT EmpName, EmpSalary ,rank() over(order by EmpName) as rankID from Employe

The following is the OUTPUT of the above query.

Gap represents number of occurrence example - EmpName="atul" is repeated 3 times and has rank "1" , the next rank will be 1+3=4 and same with the next value.

Dense_Rank() Funcation

Dense_Rank() Funcation is similar to Rank with only difference, this will not leave gaps between groups.

Example