Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
Functions, 54 of 166
lag::=
lag
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.
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
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|