SQL LEAD Function 3

SQL LEAD Function

The SQL LEAD is one of the Analytic Function. This function allows you to access the data from a subsequent row without using any SELF JOIN. 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: 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.
    • 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

Customer Table Data 1

SQL LEAD without Partition By Clause

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

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

Next, the SQL lead function returns the subsequent value of the row (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 1

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

Now you can see that the lead function is replacing NULL value with 0. Please refer to 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 then 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, 14th row, SQL Server Lead function returns 0 because there are no subsequent records to select within that partition. All those records are last records of their partition.

SQL LEAD Function 3

In the above query, we used SQL LEAD Function with Partition by clause. 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]
SQL LEAD Function 4

Hope you understand :)

Lead Offset Value

What happens if we change the offset value in the SQL 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 below screenshot, the SQL server lead function is selecting the 2nd row (jumping one row) rather than selecting 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