Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
Syntax
See Also: "Analytic Functions" for information on syntax, semantics, and restrictions, including valid forms ofvalue_expr |
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
, then 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
, then its default is null.
You cannot use LAG
or any other analytic function for value_expr
. That is, you cannot nest analytic functions, but you can use other built-in function expressions for value_expr
.
Examples
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