Summary: in this tutorial, you will learn how to access data of a row at a specific physical offset that follows the current row using the SQL LEAD() function.
Overview of SQL LEAD() function
SQL LEAD() is a window function that provides access to a row at a specified physical offset which follows the current row.
For example, by using the LEAD() function, from the current row, you can access data of the next row, or the second row that follows the current row, or the third row that follows the current row, and so on.
The LEAD() function can be very useful for calculating the difference between the value of the current row and the value of the following row.
The syntax of the LEAD() function is as follows:
LEAD(return_value [,offset[, default ]]) OVER (
PARTITION BY expr1, expr2,...
ORDER BY expr1 [ASC | DESC], expr2,...
)
Code language: SQL (Structured Query Language) (sql) return_value
The return value of the following row offsetting from the current row.
offset
The number of rows forwards from the current row from which to access data. The offset must be a non-negative integer. If you don’t specify offset, it defaults to 1.
default
The function returns default if the offset goes beyond the scope of the partition. If you do not specify default, NULL is returned.
PARTITION BY clause
The PARTITION BY clause divides rows of the result set into partitions to which the LEAD() function applies. If you do not specify the PARTITION BY clause, the whole result set is treated as a single partition.
ORDER BY clause
The ORDER BY clause sorts the rows in each partition to which the LEAD() function applies.
SQL LEAD() function examples
We will use the employees table from the sample database for the demonstration purposes.
A) Using SQL LEAD() function over result set example
The following statement returns, for each employee in the company, the hire date of the employee hired just after:
SELECT
first_name,
last_name,
hire_date,
LEAD(hire_date, 1) OVER (
ORDER BY hire_date
) AS next_hired
FROM
employees;
Code language: SQL (Structured Query Language) (sql)The following shows the output:
In this example, we omitted the PARTITION BY clause, therefore, the whole result was treated as a single partition. The ORDER BY clause sorted employees by hire dates in ascending order. The LEAD() function applied to each row in the result set.
B) Using SQL LEAD() function over partition example
The following statement provides, for each employee, the hire date of the employee in the same department which was hired just after:
SELECT
first_name,
last_name,
department_name,
hire_date,
LEAD(hire_date, 1, 'N/A') OVER (
PARTITION by department_name
ORDER BY hire_date
) AS next_hire_date
FROM
employees e
INNER JOIN departments d ON
d.department_id = e.department_id;
Code language: SQL (Structured Query Language) (sql)The following picture shows the partial output:
In this example, we used the PARTITION BY clause to divide the employees by departments into partitions and used the ORDER BY clause to sort the employees in each department by hire dates in ascending order. The LEAD() function was applied to each sorted partitions independently to get the next hire dates of the employees in each department.
In this tutorial, you have learned how to use the SQL LEAD() function to access data of the forward row from the current row.
