SQL LEAD Function 3

SQL LEAD Function

by

The SQL LEAD is one of the Analytic Function. This LEAD 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 single value in SQL Server.
  • Default: Specify a default value. If you omit, SQL write 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 SQL SELECT Statement into partitions.
    • If you defined the Partition By Clause, SQL Server LEAD Function starts selecting the subsequent rows in each partition.
    • If you haven’t, the LEAD function considers all the records as a single partition.

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

SQL LEAD 1

SQL LEAD without Partition By Clause

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

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

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

ORDER BY [YearlyIncome]

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, the Lead function returns NULL 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 is 0

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

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 lead function Query will partition 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 for their partition.

SQL LEAD Function 3

Here, we used SQL LEAD Function with Partition by clause. It selects the subsequent rows within 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.

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 :)

SQL LEAD with Offset Value

What happens if we change the offset value in the LEAD Function 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