Row_Number(),Rank(),Dense_Rank(),Lead(),Lag() Function In SQL

Introduction

In this article, we will learn about some Windows SQL functions, including Row_Number() ,Rank(),Dense_Rank(),Lead() and Leg().

Creating a table in SQL Server

Create StudentData Table As Per Below,

Create Table StudentData
(
	StdId Int,
	StdName Varchar(20),
	Class Int,
	Sub1 Int,
	Sub2 Int,
	Sub3 Int,
	Total Int
)

Insert Into StudentData Values(1,'Uday',9,85,86,90,0)
Insert Into StudentData Values(2,'Nishchay',9,56,65,50,0)
Insert Into StudentData Values(3,'Brinda',9,70,75,85,0)
Insert Into StudentData Values(4,'Jeet',9,30,29,28,0)
Insert Into StudentData Values(5,'Jay',9,50,56,65,0)
Insert Into StudentData Values(6,'Nisha',9,85,86,90,0)
Insert Into StudentData Values(7,'Nilesh',10,50,98,85,0)
Insert Into StudentData Values(8,'Jaya',10,76,98,85,0)
Insert Into StudentData Values(9,'Jayesh',10,85,76,98,0)
Insert Into StudentData Values(10,'Rahul',11,56,65,50,0)
Insert Into StudentData Values(11,'Lokesh',11,35,50,55,0)
Select * From StudentData

 The following is the sample data for the StudentData Table:

 

Student Data Table

ROW_NUMBER() Function without Partition By clause

The Row_Number() Function is a very important part of SQL. Using row_number(), we will give unique numbers to each row.

Syntax

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

Example

select *, ROW_NUMBER() over(Order By StdId) as Rownumber from StudentData

The following is the output of the above query:

Row_Number() Without Partition Table Output

ROW_NUMBER() Function with Partition By clause

If we want to add row_number() for each group, this will start from the beginning for every group. Let's understand this further by following an example.

Example

select *, ROW_NUMBER() over(Partition By Class Order By StdId) as Rownumber from StudentData 

The following is the output of the above query:

Row_Number() With Partition Table Output

RANK() Function 

The RANK() Function gives a unique value to each different row. Skip the value for the same records and give the next value. For example, if two records have same rank, e.g., 1, so it gives 3 to the third row, skip the 2.

Example

Select StdId,StdName,Class,Total,Rank() Over(Partition By Class Order By Total Desc) As RankId From StudentData

Output

Rank Output

DENSE_RANK() Function 

The DENSE_RANK() Function is similar to Rank(), but the difference is that it will not skip the number. For example, if two records have same rank, e.g., 1, it will give 2 to the third row.

Example

Select StdId,StdName,Class,Total,Dense_Rank() Over(Partition By Class Order By Total Desc) As DenseRankId From StudentData

Output

Dense Rank

Now let's take example of all three functions together and check the differences between them.

Example

Select StdId,StdName,Class,Total, ROW_NUMBER() over(Partition By Class Order By Total Desc) As Rownumber,
Rank() Over(Partition By Class Order By Total Desc) As RankId,
Dense_Rank() Over(Partition By Class Order By Total Desc) As DenseRankId
From StudentData

Output

All Function

LEG() Function

The Lag() function is used to access a previous row's data, as per a defined offset value. This is a useful function to use in comparing the current row value to the previous row value.

Syntax

LAG (scalar_expression [,offset] [,default])

Example Without Any Argument

Select StdId,StdName,Class,Total,Lag(Total) Over (Partition by Class Order By StdId) As Prev_Total
From  StudentData S

Output

Leg Function

You see it gives null value where there are no previous values. So for that, we have to give the default value.

Example With Argument

Select StdId,StdName,Class,Total,Lag(Total,2,0) Over (Partition by Class Order By StdId) As Prev_Total
From  StudentData S

Output

Lag Function

LEAD() Function

The LEAD() function is same as Lead(), but it is used to access the next row's data, as per defined offset value. This is a useful function in comparing the current row's value to the next row's value.

Syntax

LEAD (scalar_expression [,offset] [,default])

Example Without Any Argument

Select StdId,StdName,Class,Total,Lead(Total) Over (Partition by Class Order By StdId) As Next_Total
From  StudentData S

Output

lead function

You see it gives null value where there are no next values. So for that, we have to give the default value.

Example With Argument

Select StdId,StdName,Class,Total,Lead(Total,2,0) Over (Partition by Class Order By StdId) As Next_Total
From  StudentData S

Output

lead function

Summary

In this article, we learned what Row_Number() ,Rank(), and Dense_Rank() are, and what the differences between them are. We also learned how to use the LEG() And LEAD() Function in SQL Server.