|Oracle® Database SQL Language Reference
11g Release 1 (11.1)
|PDF · Mobi · ePub|
See Also:"Analytic Functions" for information on syntax, semantics, and restrictions, including valid forms of
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 nest analytic functions by using
LEAD or any other analytic function for
value_expr. However, you can use other built-in function expressions for
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-97 15-NOV-98 Colmenares 10-AUG-99 Himuro 15-NOV-98 10-AUG-99 Khoo 18-MAY-95 24-JUL-97 Raphaely 07-DEC-94 18-MAY-95 Tobias 24-JUL-97 24-DEC-97