totn Oracle Functions

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
An expression that can contain other built-in functions, but can not contain any analytic functions.
offset
Optional. It is the physical offset from the current row in the table. If this parameter is omitted, the default is 1.
default
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.
query_partition_clause
Optional. It is used to partition the results into groups based on one or more expressions.
order_by_clause
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.