Oracle / PLSQL: LAG Function

This Oracle tutorial explains how to use the Oracle/PLSQL LAG function with syntax and examples.

Description

The Oracle/PLSQL LAG 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 a previous row in the table. To return a value from the next row, try using the LEAD function.

Syntax

The syntax for the LAG function in Oracle/PLSQL is:

LAG ( 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 LAG function returns values from a previous row in the table.

Applies To

The LAG function can be used in the following versions of Oracle/PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Example

The LAG function can be used in Oracle/PLSQL.

Let's look at an example. If we had an orders table that contained the following data:

And we ran the following SQL statement:

SELECT product_id, order_date,
LAG (order_date,1) OVER (ORDER BY order_date) AS prev_order_date
FROM orders;

It would return the following result:

In this example, the LAG function will sort in ascending order all of the order_date values in the orders table and then return the previous 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 earlier. If we had used an offset of 3, it would have returned the order_date from 3 orders earlier....and so on.

Using Partitions

Now let's look at a more complex example where we use a query partition clause to return the previous order_date for each product_id.

Enter the following SQL statement:

SELECT product_id, order_date,
LAG (order_date,1) OVER (PARTITION BY product_id ORDER BY order_date) AS prev_order_date
FROM orders;

It would return the following result:

In this example, the LAG 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 LAG 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 LAG function will restart its calculations and use the appropriate product_id partition.

As you can see, the 1st record in the result set has a value of NULL for the prev_order_date because it is the first record for the partition where product_id is 1000 (sorted by order_date) so there is no lower order_date value. This is also true for the 4th record where the product_id is 2000.