Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
Syntax
See Also: "Analytic Functions" for information on syntax, semantics, and restrictions, including valid forms ofvalue_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.
You cannot use LEAD
or any other analytic function for value_expr
. That is, you cannot nest analytic functions, but you can use other built-in function expressions for value_expr
.
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