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

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

  1. 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

  1. 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

  1. 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

  1. SELECT EmpName ,EmpSalary ,DENSE_RANK() over(order by EmpName) as DrankID from Employe  

The following is the OUTPUT of the above query:



Summary

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