SQL LEAD Function

The SQL Server LEAD is one of the Analytic functions. This function allows you to access the data from a subsequent row without using any SELF JOIN. The basic syntax of the SQL LEAD function is as shown below:

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

• Scalar Expression: Column, Subquery, or expression that returns a single value.
• Default: Specify a default value. If you omit it, the SQL Server lead function writes NULL
• Offset: Number of rows you want to forward. For instance, If it is 2, then it selects the 2nd row as a result. Again, it can be a Column, Subquery, or expression which returns a single value.
• Order_By_Clause: Sort the Partitioned data in a particular order. Please refer to Order By Clause in SQL Server.
• Partition_By_Clause: Divide the records chosen by the SELECT Statement into partitions.
  • If you define the Partition_By_Clause, the SQL LEAD Function selects the subsequent rows in each partition.
  • If you haven’t, it will consider all the records as a single partition.

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

SQL Server LEAD without Partition By Clause

What will happen if we miss the Partition By Clause in the SQL LEAD Function? The order by clause will sort the Employee table based on their Yearly Income data in Ascending order.

Next, this SQL Server LEAD method 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]

L.et me add the Offset as 1, and the default value is 0

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

Now you can see that the lead function is replacing the NULL value with 0. Please refer to the LAG and SELF JOIN articles.

SQL LEAD with Partition By Example

How do you write the subsequent values from the partitioned records in a table?

The following query will group the data by Occupation using their yearly Income and 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 there are no subsequent records to select within that partition. Therefore, all those records are the last records of their partition.

We used SQL Server LEAD Function with Partition by clause in the above query. It selects the subsequent rows within the individual partition.

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

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

Hope you understand :)

Lead Offset Value

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