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 that 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 without Partition By clause

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 the above query
ROW_NUMBER() Function with Partition By clause

Rank() Function in SQL Server

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.
Rank() Function in SQL Server
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() Function in SQL Server

Dense_Rank() Function 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:
Dense_Rank() Function in SQL Server

Summary

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