LAG FUNCTION& LEAD FUNCTION in oracle

LAG FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL LAG function with syntax and examples.

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.

Examples

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

)

LEAD FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL LEAD function with syntax and examples.

DESCRIPTION

LEAD 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, LEAD provides access to a row at a given physical offset beyond that position

Examples

The following example provides, for each employee in the employees table, the hire date of the employee hired just after:

SELECT last_name, hire_date,

LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired"

FROM employees WHERE department_id = 30;

LAST_NAME HIRE_DATE NextHired

------------------------- --------- ---------

Raphaely 07-DEC-94 18-MAY-95

Khoo 18-MAY-95 24-JUL-97

Tobias 24-JUL-97 24-DEC-97

Baida 24-DEC-97 15-NOV-98

Himuro 15-NOV-98 10-AUG-99

Colmenares 10-AUG-99