LAG

Syntax

lag::=

Text description of lag

See Also: "Analytic Functions" for information on syntax, semantics, and restrictions

Purpose

LAG is an analytic function. It provides access to more than one row of a table at the same time without a self-join. Given a series of rows returned from a query and a position of the cursor, LAG provides access to a row at a given physical offset prior to that position.

If you do not specify offset, its default is 1. The optional default value is returned if the offset goes beyond the scope of the window. If you do not specify default, its default value is null.

You cannot use LAG or any other analytic function for value_expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions.

See Also: "About SQL Expressions" for information on valid forms of expr

Example

The following example provides, for each salesperson in the employees table, the salary of the employee hired just before:

SELECT last_name, hire_date, salary,
   LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal
   FROM employees
   WHERE job_id = 'PU_CLERK';
   
LAST_NAME                 HIRE_DATE     SALARY   PREV_SAL
------------------------- --------- ---------- ----------
Khoo                      18-MAY-95       3100          0
Tobias                    24-JUL-97       2800       3100
Baida                     24-DEC-97       2900       2800
Himuro                    15-NOV-98       2600       2900
Colmenares                10-AUG-99       2500       2600