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

Introduction

In this article, we will learn about SQL functions, Row_Number(), Rank(), Dense_Rank(), Lead() and Leg() with their examples. If you are new to SQL, please read and download a free SQL book here:  What is SQL. Everything You Need to Know About SQL.

Creating a table in SQL Server

To get this starrted, let's create a new table, StudentData in SQL Server using the following SQL statement. You can run this query in SSMS or console, however you like to work with SQL Server. The StudentData tabe has seven columns - StdId, StdName, Class, Sub1, Sub2, Sub3, and Total that represent ID, student name, class, subject1, subject2, subject3, and total marks of a student respectively. The query also ads few records to the table.

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)

Let's check our data. Execute the following query.

Select * From StudentData

 The following is the sample data for the StudentData Table that we just added using the above query.

ROW_NUMBER() Function without Partition By clause

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

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() 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.

RANK() Function in SQL

The RANK() function in SQL 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 the 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

DENSE_RANK() Function in SQL

The DENSE_RANK() function in SQL is similar to Rank(), but the difference is that it will not skip the number. For example, if two records have the 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

Now let's take the 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

LEG() Function in SQL

The Lag() function in SQL 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

You see it gives a 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

LEAD() Function in SQL

The LEAD() function in SQL is used to access the next row's data, as per the 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

You see it gives a 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

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

Want to Master SQL? Here is an intro article with couple of free book downloads: What is SQL. Everything You Need to Know About SQL.