SQL LEAD Function 3

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 LEAD 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, it 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 defined the Partition_By_Clause, the LEAD Function starts selecting the subsequent rows in each partition.
    • It will consider all the records as a single partition if you haven’t.

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

Customer Table Data 1

SQL Server LEAD without Partition By Clause

What will happen if we miss the Partition By Clause in the LEAD Function?

The order by the statement will sort the Employee table based on their Yearly Income data in Ascending order.

Next, this method returns the subsequent row value (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]
SQL LEAD Function OVER ORDER BY 1

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]
Offset as 1 and the default value is 0

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 to write the subsequent values from the partitioned records present 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 row, 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.

LEAD With Partition By Clause 3

We used 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]
Offset Example 4

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

SQL LEAD Function 5