Skip Headers
Oracle® Database SQL Language Reference
11g Release 2 (11.2)

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

Go to previous page
Previous
Go to next page
Next
View PDF

LEAD

Syntax

Description of lead.gif follows
Description of the illustration lead.gif

See Also:

"Analytic Functions" for information on syntax, semantics, and restrictions, including valid forms of value_expr

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.

See Also:

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

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
   ORDER BY last_name, hire_date, "NextHired";

LAST_NAME                 HIRE_DATE NextHired
------------------------- --------- ---------
Baida                     24-DEC-05 15-NOV-06
Colmenares                10-AUG-07
Himuro                    15-NOV-06 10-AUG-07
Khoo                      18-MAY-03 24-JUL-05
Raphaely                  07-DEC-02 18-MAY-03
Tobias                    24-JUL-05 24-DEC-05