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.
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:
| Ordered Date | Product Code | QTY |
|---|---|---|
| 2019/01/02 | 1000 | 20 |
| 26-MAR-2019 | 2000 | 15 |
| 2007/09/27 | 1000 | 8 |
| 2019/04/22 | 2000 | 12 |
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 Code | Ordered Date | NEXT Ordered Date |
|---|---|---|
| 2000 | 26-MAR-2019 | 4/26/2019 |
| 1000 | 2/2/2019 | 3/2/2019 |
| 2000 | 4/26/2019 | 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.
