SQL LEAD Function

The SQL Server LEAD function is one of the Analytic functions. It allows you to access the data from a subsequent row without using any SELF JOIN or Subquery. The LEAD function is handy when comparing the current and next row values.

The SQL Server LEAD function has to be followed by the ORDER BY clause and an optional PARTITION BY clause. The ORDER BY clause sorts the table or partition in Ascending or Descending order based on the given column, and the SQL LEAD function retrieves the next row within the partition following that particular order.

In real-time, you can use this LEAD function to compare current sales with the next row of sales to identify the sales trend over time. This article explains the SQL Server LEAD function syntax, parameters, and practical examples.

SQL LEAD function syntax

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

SELECT LEAD([Scalar Expression], [Offset], [Default]) 
                  OVER (
                         PARTITION_BY_Clause 
                         ORDER_BY_Clause
                        )
FROM [Source]

Let’s see the SQL LEAD Function syntax parameters and their significance in a query.

  • Scalar Expression: Please specify the Column Name, Subquery, or expression from which you want to retrieve the successive row(s) value. It returns a single value.
  • Default: It is an optional parameter; here, you must specify a default value. By default, if no subsequent row is available, the SQL LEAD function returns NULL. However, if you pass the default value, NULLs will replace with this value. If you omit default_value, this function writes NULL.
  • Offset: An optional parameter determines the number of rows you want to look forward to. For instance, an offset of 1 means the next row, and 2 means it selects the 2nd row (i.e., row after the next row). Again, it can be a Column, Subquery, or expression which returns a single value.
  • Partition_By_Clause: It allows you to divide the table records chosen by the SELECT Statement into multiple partitions based on the given column. If you define the Partition By Clause, the SQL LEAD Function is bound to that group and selects the subsequent rows in each partition. It will consider all the table records as a single partition if you don’t. 
  • Order_By_Clause: A mandatory parameter that helps to sort the Partitioned data in ascending or descending order. Please refer to Order By Clause in SQL Server. The LEAD function returns the following row based on this order.

SQL Server LEAD Function Examples

This section covers multiple examples to better understand the SQL LEAD function and how the PARTITION BY clause changes the complete result set. We also explain the importance of the Offset and default values in. handling NULLs. For this LEAD Function demonstration, we will use the below-shown data.

Customer Table Data 1

SQL LEAD Function without a Default Value

This example explains what will happen if we miss the Partition By Clause and Default value in the SQL LEAD Function.

First, the ORDER BY [YearlyIncome] will sort the Employee table based on their Yearly Income value in Ascending order. Next, the SQL Server LEAD function returns the subsequent row value (the next value from the row) as the output. If there are no rows to return, it returns NULL because we haven’t defined any default value.

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

You can see from the below image the Compare Sale of Gail Erickson is NULL because there is no next row and default value.

SQL LEAD Function OVER ORDER BY without Default Value

SQL LEAD Function with a Default Value to handle NULLs

Let me modify the above SQL LEAD Function example to add the Offset as 1, and the default value is 0. Although the default value of Offset is 1, you have to mention it before using the default value because it is the second argument. If you miss the Offset value, 0 becomes offset, and no default value will replace NULLs.

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

Now you can see the SQL LEAD function replaces the NULL value of Gail Erickson with 0. It means the Default Value Parameter will handle the NULLs returned by the LEAD function. Please refer to the LAG Function and SELF JOIN articles.

SQL Server LEAD Function with Offset as 1 and the default value is 0 to handle NULLs

SQL LEAD Function with Partition By Clause

This example explains using the SQL LEAD function to return the following values from the partitioned records in a table. In all the above examples, we didn’t use the PARTITION BY clause, so this function considers the whole table as a single partition. When you use PARTITION BY clause, it groups the table records based on the given column.

The following query will group (partition) the Employee table data by Occupation. Next, the ORDER BY clause will sort each partition based on their Yearly Income value in Ascending order. Finally, the SQL LEAD function retrieves the subsequent Sales values in each partition.

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

As you can see, for the fourth, 8th, 12th, and 14th rows, the SQL Server LEAD function returns 0 because all those records are the last records of their partition. Therefore, there are no subsequent records to select within that partition.

SQL LEAD Function With Partition By Clause

If you miss providing the Offset and default values, those 0’s will replace by NULLs. For this, replace LEAD([Sales], 1, 0) with LEAD([Sales]) in the above code.

The Role of PARTITION BY

The above query explains the SQL Server LEAD Function with Partition by clause. It selects the subsequent rows within the individual Occupation group. However, if we change the PARTITION BY column, it returns an entirely new Sales value. Let me change the partition column to Education to understand it better.

The below query returns the Sales of subsequent rows for each Eduction group instead of the Occupation partition.

SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[Education]
      ,[YearlyIncome]
      ,[Sales]
      ,LEAD([Sales], 1, 0) OVER (
				  PARTITION BY [Education]
				  ORDER BY [YearlyIncome]
				) AS CompareSale  
FROM [Employee]
The Role of PARTITION BY 4

I hope you understand :)

SQL LEAD Function With Offset Value 2

As we mentioned before, the default offset value is 1. What happens if we change the offset value in the SQL Server LEAD Function offset value from default 1 to 2?

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

If you observe the screenshot below, the SQL Server LEAD function selects the 2nd row (jumping one row) rather than the next subsequent row. If you change the value to 3, it selects the third row by dropping the first two rows, etc.

The Skilled Manual group has only two rows. For Ruben Torres, it tries to retrieve the 2nd row(15th row) by skipping the 14th row. No records are in the 15th row, so it returns the default 0.

SQL LEAD Function with Offset Value 2
Categories SQL