LEAD

Returns a value from a subsequent row within the partition of a result set.

See also: LAG.

Syntax

LEAD ( expr [, offset ])
   OVER (
         [ PARTITION BY partition_expr [, ...] ]
         [ ORDER BY order [ ASC | DESC ] [, ...]]
      )

offset ::= integer literal

Arguments

Parameter

Description

expr

A TINYINT, SMALLINT, INT, BIGINT, REAL, DOUBLE, BOOL, DATE, DATETIME or TEXT expression. Arrays and DATETIME2 are not supported.

offset

Specifies the offset of rows to scan forward. If not specified, defaults to 1.

Returns

Same type as expr.

Notes

  • offset is evaluated with respect to the current row. If not specified, offset defaults to 1.

  • If there is no subsequent row (calculated against the current row), LEAD returns NULL.

Examples

For these examples, assume a table named nba, with the following structure:

CREATE TABLE nba
(
   "Name" text(40),
   "Team" text(40),
   "Number" tinyint,
   "Position" text(2),
   "Age" tinyint,
   "Height" text(4),
   "Weight" real,
   "College" text(40),
   "Salary" float
 );

Here’s a peek at the table contents (Download nba.csv):

nba.csv

Name

Team

Number

Position

Age

Height

Weight

College

Salary

Avery Bradley

Boston Celtics

0.0

PG

25.0

6-2

180.0

Texas

7730337.0

Jae Crowder

Boston Celtics

99.0

SF

25.0

6-6

235.0

Marquette

6796117.0

John Holland

Boston Celtics

30.0

SG

27.0

6-5

205.0

Boston University

R.J. Hunter

Boston Celtics

28.0

SG

22.0

6-5

185.0

Georgia State

1148640.0

Jonas Jerebko

Boston Celtics

8.0

PF

29.0

6-10

231.0

5000000.0

Amir Johnson

Boston Celtics

90.0

PF

29.0

6-9

240.0

12000000.0

Jordan Mickey

Boston Celtics

55.0

PF

21.0

6-8

235.0

LSU

1170960.0

Kelly Olynyk

Boston Celtics

41.0

C

25.0

7-0

238.0

Gonzaga

2165160.0

Terry Rozier

Boston Celtics

12.0

PG

22.0

6-2

190.0

Louisville

1824360.0

Using LEAD to access next rows

Changed in version 2020.2: LAG and LEAD are supported from v2020.2.

This example calculates the salary between two players, starting from the highest salary.

t=> SELECT "Name",
.          "Salary",
.          LEAD("Salary") OVER (ORDER BY "Salary" DESC) AS "Salary - next",
.          "Salary" - LEAD("Salary",1) OVER (ORDER BY "Salary" DESC) AS "Salary - diff"
.          -- LEAD("Salary",1) is equivalent to LEAD("Salary")
.   FROM   nba
.   LIMIT 11 ;
Name            | Salary   | Salary - next | Salary - diff
----------------+----------+---------------+--------------
Kobe Bryant     | 25000000 |      22970500 |       2029500
LeBron James    | 22970500 |      22875000 |         95500
Carmelo Anthony | 22875000 |      22359364 |        515636
Dwight Howard   | 22359364 |      22192730 |        166634
Chris Bosh      | 22192730 |      21468695 |        724035
Chris Paul      | 21468695 |      20158622 |       1310073
Kevin Durant    | 20158622 |      20093064 |         65558
Derrick Rose    | 20093064 |      20000000 |         93064
Dwyane Wade     | 20000000 |      19689000 |        311000
Brook Lopez     | 19689000 |      19689000 |             0
DeAndre Jordan  | 19689000 |      19689000 |             0