LEAD [analytic]

Returns values from the row after the current row within a , letting you access more than one row in a table at the same time.

Returns values from the row after the current row within a window, letting you access more than one row in a table at the same time. This is useful for comparing values when the relative positions of rows can be reliably known. It also lets you avoid the more costly self join, which enhances query processing speed.

Behavior type

Immutable

Syntax

LEAD ( expression[, offset ] [, default ] ) OVER (
    [ window-partition-clause ]
    window-order-clause )

Parameters

expression
The expression to evaluate—for example, a constant, column, non-analytic function, function expression, or expressions involving any of these.
offset
Is an optional parameter that defaults to 1 (the next row). This parameter must evaluate to a constant positive integer.
default
The value returned if offset falls outside the bounds of the table or partition. This value must be a constant value or an expression that can be evaluated to a constant; its data type is coercible to that of the first argument.

Examples

LEAD finds the hire date of the employee hired just after the current row:


=> SELECT employee_region, hire_date, employee_key, employee_last_name,
   LEAD(hire_date, 1) OVER (PARTITION BY employee_region ORDER BY hire_date) AS "next_hired"
   FROM employee_dimension ORDER BY employee_region, hire_date, employee_key;
  employee_region  | hire_date  | employee_key | employee_last_name | next_hired
-------------------+------------+--------------+--------------------+------------
 East              | 1956-04-08 |         9218 | Harris             | 1957-02-06
 East              | 1957-02-06 |         7799 | Stein              | 1957-05-25
 East              | 1957-05-25 |         3687 | Farmer             | 1957-06-26
 East              | 1957-06-26 |         9474 | Bauer              | 1957-08-18
 East              | 1957-08-18 |          570 | Jefferson          | 1957-08-24
 East              | 1957-08-24 |         4363 | Wilson             | 1958-02-17
 East              | 1958-02-17 |         6457 | McCabe             | 1958-06-26
 East              | 1958-06-26 |         6196 | Li                 | 1958-07-16
 East              | 1958-07-16 |         7749 | Harris             | 1958-09-18
 East              | 1958-09-18 |         9678 | Sanchez            | 1958-11-10
(10 rows)

The next example uses LEAD and LAG to return the third row after the salary in the current row and fifth salary before the salary in the current row.

=> SELECT hire_date, employee_key, employee_last_name,
   LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "next_hired" ,
   LAG(hire_date, 1) OVER (ORDER BY hire_date) AS "last_hired"
   FROM employee_dimension ORDER BY hire_date, employee_key;
 hire_date  | employee_key | employee_last_name | next_hired | last_hired
------------+--------------+--------------------+------------+------------
 1956-04-11 |         2694 | Farmer             | 1956-05-12 |
 1956-05-12 |         5486 | Winkler            | 1956-09-18 | 1956-04-11
 1956-09-18 |         5525 | McCabe             | 1957-01-15 | 1956-05-12
 1957-01-15 |          560 | Greenwood          | 1957-02-06 | 1956-09-18
 1957-02-06 |         9781 | Bauer              | 1957-05-25 | 1957-01-15
 1957-05-25 |         9506 | Webber             | 1957-07-04 | 1957-02-06
 1957-07-04 |         6723 | Kramer             | 1957-07-07 | 1957-05-25
 1957-07-07 |         5827 | Garnett            | 1957-11-11 | 1957-07-04
 1957-11-11 |          373 | Reyes              | 1957-11-21 | 1957-07-07
 1957-11-21 |         3874 | Martin             | 1958-02-06 | 1957-11-11
(10 rows)

The following example returns employee name and salary, along with the next highest and lowest salaries.

=> SELECT employee_last_name, annual_salary,
       NVL(LEAD(annual_salary) OVER (ORDER BY annual_salary),
         MIN(annual_salary) OVER()) "Next Highest",
       NVL(LAG(annual_salary) OVER (ORDER BY annual_salary),
         MAX(annual_salary)  OVER()) "Next Lowest"
   FROM employee_dimension;
 employee_last_name | annual_salary | Next Highest | Next Lowest
--------------------+---------------+--------------+-------------
 Nielson            |          1200 |         1200 |      995533
 Lewis              |          1200 |         1200 |        1200
 Harris             |          1200 |         1202 |        1200
 Robinson           |          1202 |         1202 |        1200
 Garnett            |          1202 |         1202 |        1202
 Weaver             |          1202 |         1202 |        1202
 Nielson            |          1202 |         1202 |        1202
 McNulty            |          1202 |         1204 |        1202
 Farmer             |          1204 |         1204 |        1202
 Martin             |          1204 |         1204 |        1204
(10 rows)

The next example returns, for each assistant director in the employees table, the hire date of the director hired just after the director on the current row. For example, Jackson was hired on 2016-12-28, and the next director hired was Bauer:

=> SELECT employee_last_name, hire_date,
       LEAD(hire_date, 1) OVER (ORDER BY hire_date DESC) as "NextHired"
   FROM employee_dimension WHERE job_title = 'Assistant Director';
 employee_last_name | hire_date  | NextHired
--------------------+------------+------------
 Jackson            | 2016-12-28 | 2016-12-26
 Bauer              | 2016-12-26 | 2016-12-11
 Miller             | 2016-12-11 | 2016-12-07
 Fortin             | 2016-12-07 | 2016-11-27
 Harris             | 2016-11-27 | 2016-11-15
 Goldberg           | 2016-11-15 |
(5 rows)

See also