LAG

Syntax

For information on syntax and semantics, see "Analytic Functions".

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.

Example

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

SELECT ename, hiredate, sal, 
   LAG(sal, 1, 0) OVER (ORDER BY hiredate) as prev_sal
   FROM emp
   WHERE job = 'SALESMAN';

ENAME      HIREDATE         SAL   PREV_SAL
---------- --------- ---------- ----------
ALLEN      20-FEB-81       1600          0
WARD       22-FEB-81       1250       1600
TURNER     08-SEP-81       1500       1250
MARTIN     28-SEP-81       1250       1500