LAG analytic function example

View All Scripts Login to Run Script

Script Name LAG analytic function example
Description 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.
Area SQL General / Analytics
Contributor Mike Hichwa (Oracle)
Created Monday October 05, 2015

Statement 1

SELECT last_name, hire_date, salary, 
   LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal 
   FROM hr. employees 
   WHERE job_id = 'PU_CLERK'

 
LAST_NAME HIRE_DATE SALARY PREV_SAL
Khoo 18-MAY-03 3100 0
Tobias 24-JUL-05 2800 3100
Baida 24-DEC-05 2900 2800
Himuro 15-NOV-06 2600 2900
Colmenares 10-AUG-07 2500 2600

5 rows selected.

LAST_NAME	HIRE_DATE	SALARY	PREV_SAL
Khoo	18-MAY-03	3100	0
Tobias	24-JUL-05	2800	3100
Baida	24-DEC-05	2900	2800
Himuro	15-NOV-06	2600	2900
Colmenares	10-AUG-07	2500	2600

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database