Oracle Analytical Function LAG With Example

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.
For the optional offset argument, specify an integer that is greater than zero. 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.

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'
  ORDER BY last_name, hire_date, salary;
   
LAST_NAME                 HIRE_DATE     SALARY   PREV_SAL
------------------------- --------- ---------- ----------
Baida                     24-DEC-05       2900       2800
Colmenares                10-AUG-07       2500       2600
Himuro                    15-NOV-06       2600       2900
Khoo                      18-MAY-03       3100          0
Tobias                    24-JUL-05       2800       3100