Oracle8i SQL Reference
Release 2 (8.1.6)

A76989-01

Library

Product

Contents

Index

Prev Up Next

Functions, 37 of 121


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

Prev Up Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index