Oracle Plsql Lead Function
Oracle / PLSQL: LEAD Function
This Oracle tutorial explains how to use the Oracle/PLSQL LEAD function with syntax and examples.
Description
The Oracle/PLSQL 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 Oracle/PLSQL 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.
Returns
The LEAD function returns values from the next row in the table.
Applies To
The LEAD function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Example
The LEAD function can be used in Oracle/PLSQL.
Let's look at an example. If we had an orders table that contained the following data:
| ORDER_DATE | PRODUCT_ID | QTY |
|---|---|---|
| 2007/09/25 | 1000 | 20 |
| 2007/09/26 | 2000 | 15 |
| 2007/09/27 | 1000 | 8 |
| 2007/09/28 | 2000 | 12 |
| 2007/09/29 | 2000 | 2 |
| 2007/09/30 | 1000 | 4 |
And we ran the following SQL statement:
SELECT product_id, order_date,
LEAD (order_date,1) OVER (ORDER BY order_date) AS next_order_date
FROM orders;It would return the following result:
| PRODUCT_ID | ORDER_DATE | NEXT_ORDER_DATE |
|---|---|---|
| 1000 | 2007/09/25 | 2007/09/26 |
| 2000 | 2007/09/26 | 2007/09/27 |
| 1000 | 2007/09/27 | 2007/09/28 |
| 2000 | 2007/09/28 | 2007/09/29 |
| 2000 | 2007/09/29 | 2007/09/30 |
| 1000 | 2007/09/30 | NULL |
In this example, the LEAD function will sort in ascending order all of the order_date values in the orders table and then return the next order_date since we used an offset of 1.
If we had used an offset of 2 instead, it would have returned the order_date from 2 orders later. If we had used an offset of 3, it would have returned the order_date from 3 orders later....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 order_date for each product_id.
Enter the following SQL statement:
SELECT product_id, order_date,
LEAD (order_date,1) OVER (PARTITION BY product_id ORDER BY order_date) AS next_order_date
FROM orders;It would return the following result:
| PRODUCT_ID | ORDER_DATE | NEXT_ORDER_DATE |
|---|---|---|
| 1000 | 2007/09/25 | 2007/09/27 |
| 1000 | 2007/09/27 | 2007/09/30 |
| 1000 | 2007/09/30 | NULL |
| 2000 | 2007/09/26 | 2007/09/28 |
| 2000 | 2007/09/28 | 2007/09/29 |
| 2000 | 2007/09/29 | NULL |
In this example, the LEAD function will partition the results by product_id and then sort by order_date as indicated by PARTITION BY product_id ORDER BY order_date. This means that the LEAD function will only evaluate an order_date value if the product_id matches the current record's product_id. When a new product_id is encountered, the LEAD function will restart its calculations and use the appropriate product_id partition.
As you can see, the 3rd record in the result set has a value of NULL for the next_order_date because it is the last record for the partition where product_id is 1000 (sorted by order_date). This is also true for the 6th record where the product_id is 2000.
