Rajendra Gupta
Lead function with Partition by clause

SQL Server Lead function overview and examples

July 16, 2019 by

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.

Sample data

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.

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.

SQL Server Lead function with a default value

Example 2: Lead function with a default value

Let’s specify a default value for the argument default and rerun the query.

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.

In this output, we get the specified default date instead of the NULL value.

specify a default value with the lead function

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.

We have following sample data in ProductSales table.

Lead function and Partition by clause

Let’s run the SQL Server Lead function and view the output.

We have use partitions in this query, and Lead function treat completes data as a single partition and returns the next quarter sales value.

Lead function without  Partition by clause

Suppose we want to analyze data for a yearly basis. We can partition the data on the Year column using the PARTITION BY clause.

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.

Lead function with  Partition by clause

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.

Lead function and sort data in ascending or desending order

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

Define CTE with lead function

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.

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.

SQL Server Lead function and specify OFFSET argument value

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

Lead function with expressions

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

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
168 Views