Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page Go to next page
View PDF




Text description of functions131.gif follows
Text description of lead

See Also:

"Analytic Functions" for information on syntax, semantics, and restrictions


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.

You cannot use LEAD or any other analytic function for value_expr. That is, you can use other built-in function expressions for value_expr, but you cannot nest analytic functions.

See Also:

"About SQL Expressions" for information on valid forms of expr


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