SQL Server LEAD() Function

The LEAD() function is a window function in SQL Server that retrieves data from the row after the current row. It helps us to obtain the values of the next row or several next rows when processing data in tables and uses them for calculations or display purposes.

Syntax

The syntax of the LEAD() function is as follows:

LEAD (scalar_expression [, offset] [, default]) OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC|DESC], ... )

where:

• scalar_expression: the column name or expression of the value to be obtained.
• offset: an optional parameter that specifies the number of rows to retrieve. The default value is 1.
• default: an optional parameter that specifies the default value to return when the retrieved value is NULL.
• PARTITION BY: an optional clause that specifies the column or expression used for grouping.
• ORDER BY: a required clause that specifies the column or expression used for sorting.

Usage

The LEAD() function is typically used in the following scenarios:

• Retrieving data from the next row: When you need to retrieve data from the row after the current row, you can use the LEAD() function. For example, when calculating the rate of return for a specific period, you can use the LEAD() function to obtain the closing price of the next day and then calculate the rate of return.
• Used with LAG() function: The LEAD() function can be used with the LAG() function to compare data from adjacent rows. For example, you can use the LEAD() function to retrieve data from the next row and compare it with the data retrieved by the LAG() function from the previous row, thus calculating the rate of change or trend.

Examples

We will demonstrate the usage of the LEAD() function through two examples.

Example 1

Suppose we have a table that contains employee names, hire dates, and salaries. We want to obtain the date and amount of the next pay raise for each employee. To do this, we can use the LEAD() function to retrieve the salary and hire date of the next employee. The following is the query and result:

SELECT
  name,
  hire_date,
  salary,
  LEAD(salary) OVER (ORDER BY hire_date) AS next_salary,
  LEAD(hire_date) OVER (ORDER BY hire_date) AS next_hire_date
FROM employees;

Query result:

Example 2

Consider an Employees table that contains information about employees. We want to find the information of the next employee for each employee, which can be accomplished using the LEAD() function.

First, we create an Employees table and insert some data into it:

CREATE TABLE Employees (
  Id INT PRIMARY KEY,
  Name VARCHAR(50),
  JobTitle VARCHAR(50),
  Salary INT
);

INSERT INTO Employees VALUES
(1, 'John', 'Manager', 5000),
(2, 'Jane', 'Assistant Manager', 4000),
(3, 'Bob', 'Developer', 3000),
(4, 'Alice', 'Developer', 3500),
(5, 'Tom', 'Developer', 3200);

Now, we can use the LEAD() function to query the information of each employee’s next employee:

SELECT Id, Name, JobTitle, Salary,
  LEAD(Name) OVER (ORDER BY Id) AS NextName,
  LEAD(JobTitle) OVER (ORDER BY Id) AS NextJobTitle,
  LEAD(Salary) OVER (ORDER BY Id) AS NextSalary
FROM Employees;

After executing the above SQL statement, we get the following results:

As we can see, each row in the result set contains the information of the current employee as well as the next employee. The next employee information in the last row is NULL because it is the last row and there is no next employee.

Conclusion

The LEAD() function is a very useful function that can help us find the successor of a certain value. By flexibly applying the LEAD() function, we can easily implement various complex queries. In practical applications, the LEAD() function is often used together with other functions, such as SUM(), AVG(), MIN(), MAX() etc., to achieve more complex query functionality.

Note that the LEAD() function can only be used in SQL Server 2012 and later versions. If an earlier version is used, other methods can be used to achieve the same functionality.