Sql Server Lead Function
SQL Server: LEAD Function
In this post explains how to use the LEAD function in SQL Server (Transact-SQL) with syntax and examples.
Description
In SQL Server (Transact-SQL), the LEAD function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from the next row in the table. To return a value from a previous row, try using the LAG function.
Syntax
The syntax for the LEAD function in SQL Server (Transact-SQL) is:
LEAD ( expression [, offset [, default] ] )
OVER ( [ query_partition_clause ] order_by_clause )Parameters or Arguments
- expression
- offset
- default
- query_partition_clause
- order_by_clause
An expression that can contain other built-in functions, but can not contain any analytic functions.
Optional. It is the physical offset from the current row in the table. If this parameter is omitted, the default is 1.
Optional. It is the value that is returned if the offset goes out of the bounds of the table. If this parameter is omitted, the default is null.
Optional. It is used to partition the results into groups based on one or more expressions.
Optional. It is used to order the data within each partition.
Applies To
The LEAD function can be used in the following versions of SQL Server (Transact-SQL):
- SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012
DDL/DML for Examples
If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!
Example
The LEAD function can be used in SQL Server (Transact-SQL).
Let's look at an example. If we had an employees table that contained the following data:
| employee_number | last_name | first_name | salary | dept_id |
|---|---|---|---|---|
| 12009 | Sutherland | Barbara | 54000 | 45 |
| 34974 | Yates | Fred | 80000 | 45 |
| 34987 | Erickson | Neil | 42000 | 45 |
| 45001 | Parker | Sally | 57500 | 30 |
| 75623 | Gates | Steve | 65000 | 30 |
And we ran the following SQL statement:
SELECT dept_id, last_name, salary,
LEAD (salary,1) OVER (ORDER BY salary) AS next_highest_salary
FROM employees;It would return the following result:
| dept_id | last_name | salary | next_highest_salary |
|---|---|---|---|
| 45 | Erickson | 42000 | 54000 |
| 45 | Sutherland | 54000 | 57500 |
| 30 | Parker | 57500 | 65000 |
| 30 | Gates | 65000 | 80000 |
| 45 | Yates | 80000 | NULL |
In this example, the LEAD function will sort in ascending order all of the salary values in the employees table and then return the next highest salary since we used an offset of 1.
If we had used an offset of 2 instead, it would have returned the salary that is 2 salaries higher. If we had used an offset of 3, it would have returned the salary that is 3 higher....and so on.
Using Partitions
Now let's look at a more complex example where we use a query partition clause to return the next highest salary for each employee within their own department.
Enter the following SQL statement:
SELECT dept_id, last_name, salary,
LEAD (salary,1) OVER (PARTITION BY dept_id ORDER BY salary) AS next_highest_salary
FROM employees;It would return the following result:
| dept_id | last_name | salary | next_highest_salary |
|---|---|---|---|
| 30 | Parker | 57500 | 65000 |
| 30 | Gates | 65000 | NULL |
| 45 | Erickson | 42000 | 54000 |
| 45 | Sutherland | 54000 | 80000 |
| 45 | Yates | 80000 | NULL |
In this example, the LEAD function will partition the results by dept_id and then sort by salary as indicated by PARTITION BY dept_id ORDER BY salary. This means that the LEAD function will only evaluate a salary value if the dept_id matches the current record's dept_id. When a new dept_id is encountered, the LEAD function will restart its calculations and use the appropriate dept_id partition.
As you can see, the 2nd record in the result set has a value of NULL for the next_highest_salary because it is the last record for the partition where dept_id is 30 (sorted by salary). This is also true for the 5th record where the dept_id is 45.
