LEAD

Syntax

Purpose

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.

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 table. If you do not specify default, then its default value is null.

{RESPECT | IGNORE} NULLS determines whether null values of value_expr are included in or eliminated from the calculation. The default is RESPECT NULLS.

You cannot nest analytic functions by using LEAD or any other analytic function for value_expr. However, you can use other built-in function expressions for value_expr.

Examples

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

SELECT hire_date, last_name,
 LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired" 
 FROM employees
 WHERE department_id = 30
 ORDER BY hire_date;
HIRE_DATE LAST_NAME Next Hired
--------- ------------------------- ----------
07-DEC-02 Raphaely 18-MAY-03
18-MAY-03 Khoo 24-JUL-05
24-JUL-05 Tobias 24-DEC-05
24-DEC-05 Baida 15-NOV-06
15-NOV-06 Himuro 10-AUG-07
10-AUG-07 Colmenares