tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment

Other Sites

CheckYourMath BigActivities DigMinecraft
Share this page:

Oracle/PLSQL: LAG Function

Learn 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 Oracle/PLSQL LAG function is:

LAG ( expression [, offset [, default] ] )
over ( [ query_partition_clause ] order_by_clause )

Parameters or Arguments

expression is an expression that can contain other built-in functions, but can not contain any analytic functions.

offset is optional. It is the physical offset from the current row in the table. If this parameter is omitted, the default is 1.

default is 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.

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:

ORDER_DATE PRODUCT_ID QTY
25/09/2007 1000 20
26/09/2007 2000 15
27/09/2007 1000 8
28/09/2007 2000 12
29/09/2007 2000 2
30/09/2007 1000 4

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:

PRODUCT_ID ORDER_DATE PREV_ORDER_DATE
1000 25/09/2007 <NULL>
2000 26/09/2007 25/09/2007
1000 27/09/2007 26/09/2007
2000 28/09/2007 27/09/2007
2000 29/09/2007 28/09/2007
1000 30/09/2007 29/09/2007

Since we used an offset of 1, the query returns the previous order_date.

If we had used an offset of 2 instead, it would have returned the order_date from 2 orders before. If we had used an offset of 3, it would have returned the order_date from 3 orders before....and so on.

If we wanted only the orders for a given product_id, we could run the following SQL statement:

select product_id, order_date,
LAG (order_date,1) over (ORDER BY order_date) AS prev_order_date
from orders
where product_id = 2000;

It would return the following result:

PRODUCT_ID ORDER_DATE PREV_ORDER_DATE
2000 26/09/2007 <NULL>
2000 28/09/2007 26/09/2007
2000 29/09/2007 28/09/2007

In this example, it returned the previous order_date for product_id = 2000 and ignored all other orders.