MySQL LEAD Function
Summary: in this tutorial, you will learn how to use the MySQL LEAD() function to access data of a subsequent row from the current row in the same result set.
Overview of MySQL LEAD() function
The LEAD() function is a window function that allows you to look forward a number of rows and access data of that row from the current row.
Similar to the LAG() function, the LEAD() function is very useful for calculating the difference between the current row and the subsequent row within the same result set.
The following shows the syntax of the LEAD() function:
LEAD(<expression>[,offset[, default_value]]) OVER (
PARTITION BY (expr)
ORDER BY (expr)
)
Code language: SQL (Structured Query Language) (sql)
expression
The LEAD() function returns the value of expression from the offset-th row of the ordered partition.
offset
The offset is the number of rows forward from the current row from which to obtain the value.
The offset must be a non-negative integer. If offset is zero, then the LEAD() function evaluates the expression for the current row.
In case you omit offset, then the LEAD() function uses one by default.
default_value
If there is no subsequent row, the LEAD() function returns the default_value. For example, if offset is one, then the return value of the last row is the default_value.
In case you do not specify the default_value, the function returns NULL .
PARTITION BY clause
The PARTITION BY clause divides the rows in the result set into partitions to which the LEAD() function is applied.
If the PARTITION BY clause is not specified, all rows in the result set is treated as a single partition.
ORDER BY clause
The ORDER BY clause determines the order of rows in partitions before the LEAD() function is applied.
MySQL LEAD() function example
We will use the orders and customers tables from the sample database for the demonstration:
The following statement finds the order date and the next order date of each customer:
SELECT
customerName,
orderDate,
LEAD(orderDate,1) OVER (
PARTITION BY customerNumber
ORDER BY orderDate ) nextOrderDate
FROM
orders
INNER JOIN customers USING (customerNumber);
Code language: SQL (Structured Query Language) (sql)
Here is the output:
In this example, we first divided the result set by the customer number into partitions. Then, we sorted each partition by the order date. Finally, the LEAD() function is applied to each partition to get the next order date.
Once the subsequent row is crossing the partition boundary, the value of the nextOrderDate in the last row of each partition is NULL.
In this tutorial, you have learned how to use the MySQL LEAD() function to access the data of the subsequent row from the current row.