Tutorial Gateway

SQL LEAD Function

by

The SQL LEAD is one of the Analytic Function in SQL Server. This function allows you to access the data from a subsequent row without using any SELF JOIN.

In this article we will show you, How to use the analytic function called LEAD in SQL Server with example. Before we get into the example, let us see the syntax behind this:

SQL LEAD Function Syntax

The basic syntax of the LEAD in SQL Server is as shown below:

SELECT LEAD([Scalar Expression], [Offset], [Default]) 
                  OVER (
                         PARTITION_BY_Clause 
                         ORDER_BY_Clause
                        )
FROM [Source]
  • Scalar Expression: It can be a Column, Subquery, or any expression that returns single value.
  • Offset: Please specify the number of rows you want to forward. For example, If it is 2 then it will select 2nd row as the result. It can be a Column, Subquery, or any expression that return a single value.
  • Default: You can specify the default value. If you omit then SQL will write NULL
  • Partition_By_Clause: This will divide the records selected by the SQL SELECT Statement into partitions.
    • If you specified the Partition By Clause, then Sql Server LEAD Function will start selecting the subsequent rows in each partition.
    • If you haven’t specified the Partition By Clause then, LEAD Function will consider all the records as single partition.
  • Order_By_Clause: This is used to sort the Partitioned data into specified order. Please refer SQL Order By Clause for better understanding.

We are going to use the below shown data for this demonstration

SQL LEAD 1

SQL LEAD without Partition By Clause

In this example we will show you, What will happen if we miss the Partition By Clause in the LEAD Function in Sql Server.

T-SQL CODE

USE [SQL Tutorial]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,LEAD([Sales]) OVER (ORDER BY [YearlyIncome]) AS CompareSale 
  FROM [Employee]

OUTPUT

SQL LEAD Function 1

ANALYSIS

Below statement will sort the Employee table based on their Yearly Income data in an Ascending order

ORDER BY [YearlyIncome]

Next, SQL lead function in the below code snippet will return the subsequent value of the row (next value from the row) as the output. If there are no rows to return, Lead function will return NULL. This is because, we haven’t defined any default value

LEAD([Sales]) OVER (ORDER BY [YearlyIncome]) AS CompareSale

Let me add the Offset as 1, and the default value as 0

T-SQL CODE

USE [SQL Tutorial]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,LEAD([Sales], 1, 0) OVER (
                                  ORDER BY [YearlyIncome]
                                 ) AS CompareSale 
FROM [Employee]

OUTPUT

SQL LEAD Function 2

Now you can see that the Sql Server lead function is replacing NULL value with 0

SQL LEAD with Partition By Example

In this Sql Server lead example we will show you, How to write the subsequent values from the partitioned records present in a table.

The following SQL lead function Query will partition the data by Occupation using their yearly Income, and then subsequent Sales values in each partition.

T-SQL CODE

USE [SQL Tutorial]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,LEAD([Sales], 1, 0) OVER (
				  PARTITION BY [Occupation]
				  ORDER BY [YearlyIncome]
				) AS CompareSale 
FROM [Employee]

OUTPUT

SQL LEAD Function 3

From the above screenshot you can observe that, for the fourth, 8th, 12th, 14th row, Sql Server Lead function returns 0 as the output. This is because there is no subsequent records to select within that partition (All those records are last records with respect their partition).

ANALYSIS

In this sql lead query, Below statement will divide the selected data into partition using their Occupation. From the above you can observe, We got four partitions

PARTITION BY [Occupation]

Below statement will sort the partitioned data in the descending order using their Yearly Income

ORDER BY [YearlyIncome]

In the below statement we used SQL LEAD Function with Partition by clause. It will select the subsequent rows with the individual partition.

LEAD([Sales], 1, 0) OVER (
			  PARTITION BY [Occupation]
			  ORDER BY [YearlyIncome]
			) AS CompareSale

Let me change the partition column to education to understand it better.

T-SQL CODE

USE [SQL Tutorial]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[Education]
      ,[YearlyIncome]
      ,[Sales]
      ,LEAD([Sales], 1, 0) OVER (
				  PARTITION BY [Education]
				  ORDER BY [YearlyIncome]
				) AS CompareSale  
FROM [Employee]

OUTPUT

SQL LEAD Function 4

Hope you understand :)

SQL LEAD with Offset Value

In this example we will show you, What will happen if we change the offset value in the SQL LEAD Function from default 1 to 2.

T-SQL CODE

USE [SQL Tutorial]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,LEAD([Sales], 2, 0) OVER (
				  PARTITION BY [Occupation]
				  ORDER BY [YearlyIncome]
			         ) AS CompareSale  
FROM [Employee]

OUTPUT

SQL LEAD Function 5

If you observe the above screenshot, the Sql server lead function is selecting the 2nd row (jumping one row), rather than selecting next subsequent row. If you change the value to 3 then it will select the third row by jumping first two rows etc.

Thank You for Visiting Our Blog

Placed Under: SQL