This article explores the SQL Server Lead function and its usage with various examples.
Introduction
We need to perform statistical and Computational operations on the data. SQL Server provides many analytical and window functions to do the task without any complexity. We can write complex queries using these window functions.
We have following Window functions in SQL Server.
- Ranking function – RANK, DENSE_RANK, ROW_Number and NTILE
- Aggregate function – SUM, MIN, MAX, AVG and Count
- Value functions – LEAD, LAG, FIRST_VALUE and LAST_VALUE
Let’s look at the SQL Server Lead function in the next section of this article.
SQL Server Lead function
The lead function is available from SQL Server 2012. This function is part of a Window function. Suppose we need to compare an individual row data with the subsequent row data, Lead function in SQL Server helps SQL developers in this regard.
Let’s prepare the sample data using the following query. In the following table, we use a table variable to define the columns, data types and insert data into that.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE @Employee TABLE ( EmpCode VARCHAR(10), EmpName VARCHAR(10), JoiningDate DATE ) INSERT INTO @Employee VALUES ('1', 'Rajendra', '1-Sep-2018') INSERT INTO @Employee VALUES ('2', 'Manoj', '1-Oct-2018') INSERT INTO @Employee VALUES ('3', 'Sonu', '10-Mar-2018') INSERT INTO @Employee VALUES ('4', 'Kashish', '25-Oct-2018') INSERT INTO @Employee VALUES ('5', 'Tim', '1-Dec-2018') INSERT INTO @Employee VALUES ('6', 'Akshita', '1-Nov-2018') SELECT * FROM @Employee; |
Let’s look at the syntax and arguments for this function.
Syntax of Lead function
LEAD(scalar_expression ,offset [,default])
OVER (
[PARTITION BY partition_expression, … ]
ORDER BY sort_expression [ASC | DESC], …
- Scalar_expression: It contains the column name or expression for which we want to apply the lead function
- Offset: It is the number of rows ahead from the current row. The lead function uses this argument to fetch the value. We can use an expression, subquery or a positive integer value in this argument. It is an optional argument. If we do not specify any value, SQL Server considers the default value as positive integer value 1
- Default: We can specify a default value for this argument. If the SQL Server Lead function crosses the boundary of a partition of no values are available, it shows the default value in the output. It is also an optional parameter and returns NULL if no values are specified
- PARTITION BY: We can create data set partitions using PARTITION BY argument. It is also an optional argument. If we do not specify this argument, SQL Server considers complete data set a single partition
- ORDER BY: Order by clause sorts the data in ascending or descending order. If we use partitions ( by PARTITION BY clause), it sorts the data in each partition
Let’s understand the SQL Server Lead function using examples.
Example 1: Lead function without a default value
In this example, execute the following query in the same window in which we declared the table variable and data. For simplicity, I do not specify the variable table declaration again and again.
|
1 2 3 4 |
SELECT *, LEAD(JoiningDate, 1) OVER( ORDER BY JoiningDate ASC) AS EndDate FROM @Employee; |
In this output, we can see that the Lead function gets the value of the subsequent row and return NULL if no subsequent row is available and the default value is also not specified.
Example 2: Lead function with a default value
Let’s specify a default value for the argument default and rerun the query.
|
1 2 3 4 |
SELECT *, LEAD(JoiningDate, 1,0) OVER( ORDER BY JoiningDate ASC) AS EndDate FROM @Employee; |
We get an error message – Operand type clash: int is incompatible with date
In the table variable, we have the JoiningDate column defined as Date. We also applied the SQL Server Lead function on the date data type. We cannot use an integer data type value for the date data type. Due to this, we get this error message of the incompatible data type.
Let’s specify the default date in this argument and rerun the query.
|
1 2 3 4 |
SELECT *, LEAD(JoiningDate, 1,'2018-01-01') OVER( ORDER BY JoiningDate ASC) AS EndDate FROM @Employee; |
In this output, we get the specified default date instead of the NULL value.
Example 3: SQL Server Lead function with PARTITION BY clause
To explain the use of the PARTITION BY clause along with the lead function, let’s create another table and insert data into it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE TABLE dbo.ProductSales ( [Year] INT ,[Quarter] TINYINT ,Sales DECIMAL(9,2) ); INSERT INTO dbo.ProductSales VALUES (2017, 1, 55000.00) ,(2017, 2, 78000.00) ,(2017, 3, 49000.00) ,(2017, 4, 32000.00) ,(2018, 1, 41000.00) ,(2018, 2, 8965.00) ,(2018, 3, 69874.00) ,(2018, 4, 32562.00) ,(2019, 1, 87456.00) ,(2019, 2, 75000.00) ,(2019, 3, 96500.00) ,(2019, 4, 85236.00) |
We have following sample data in ProductSales table.
Let’s run the SQL Server Lead function and view the output.
|
1 2 3 4 5 6 7 |
SELECT [Year], [Quarter], Sales, LEAD(Sales, 1, 0) OVER( ORDER BY [Year], [Quarter] ASC) AS [NextQuarterSales] FROM dbo.ProductSales; |
We have use partitions in this query, and Lead function treat completes data as a single partition and returns the next quarter sales value.
Suppose we want to analyze data for a yearly basis. We can partition the data on the Year column using the PARTITION BY clause.
|
1 2 3 4 5 6 7 |
SELECT [Year], [Quarter], Sales, LEAD(Sales, 1, 0) OVER(PARTITION BY [Year] ORDER BY [Year], [Quarter] ASC) AS [NextQuarterSales] FROM dbo.ProductSales; |
Let’s look at the output. In this, we can see three partitions for Year 2017,2018 and 2019. SQL Server Lead function takes subsequent value within a year partition and returns the default value for each partition. For example, in the year 2017, we get the default value for quarter 4, and it does not look for the year 2018 quarter 1.
In the previous query, we use the ORDER BY clause to sort data in ascending order. Let’s sort the data in descending order using the following query.
|
1 2 3 4 5 6 7 |
SELECT [Year], [Quarter], Sales, LEAD(Sales, 1, 0) OVER(PARTITION BY [Year] ORDER BY [Year], [Quarter] DESC) AS [NextQuarterSales] FROM dbo.ProductSales; |
Example 4: SQL Server Lead function and Common Table Expressions ( CTE)
We can use the lead function in combination with the CTE for writing complex queries. In the following query, we do the following tasks.
- Define a CTE
- Apply Lead function on the CTE to get the required values
|
1 2 3 4 5 6 7 8 9 10 11 |
WITH cte_netsales_2018 AS (SELECT [Quarter], SUM(Sales) net_sales FROM dbo.ProductSales WHERE year = 2018 GROUP BY [Quarter]) SELECT [Quarter], net_sales, LEAD(net_sales, 1, 0) OVER( ORDER BY [Quarter]) sales FROM cte_netsales_2018; |
Example 5: SQL Server Lead function and specify OFFSET argument value
In the previous examples, we used default offset value 1 to return the subsequent values. Let’s specify the offset value other than the default value and view the output.
|
1 2 3 4 5 6 7 |
SELECT [Year], [Quarter], Sales, LEAD(Sales, 2, 0) OVER( ORDER BY [Year], [Quarter] ASC) AS [NextQuarterSales] FROM dbo.ProductSales; |
For the offset value 2, it skips the next row and gets the value for the current row + 2nd row. Similarly, we can specify the offset value to get the data as per our requirement.
Example 6: SQL Server Lead function with expressions
We can use expressions as well in the lead function. In the following query, we specified the following expressions.
- Scalar_expression: To double the sales figure using the 2*sales
- Offset: we use the expression to calculate the offset value as well
|
1 2 3 4 5 6 7 8 |
1+(select min(quarter) from productsales), SELECT [Year], [Quarter], Sales, LEAD(2*sales, 1+(select min(quarter) from productsales), 0) OVER( ORDER BY [Year], [Quarter] ASC) AS [NextQuarterSales] FROM dbo.ProductSales; |
Conclusion
In this article, we explored the useful Window function SQL Server Lead along with various examples. Below is the quick summary of what we learned about SQL Server Lead function in this article:
- LEAD function get the value from the current row to subsequent row to fetch value
- We use the sort function to sort data in ascending or descending order
- We use the PARTITION BY clause to partition data based on the specified expression
- We can specify a default value to avoid NULL value in the output
- We can also address complex scnenarios with CTEs and expressions using SQL Server Lead function
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023