LEAD

Parameters

column

Column or expression whose value is taken from the next row

offsetintegeroptional

How many rows ahead to look, 1 by default; a negative value looks back, like LAG

defaultoptional

Value of the same type as column used if there is no row at that offset, NULL by default

PARTITION BYoptional

Columns that split rows into partitions, the offset does not cross partition bounds

ORDER BYoptional

Order of rows that defines which row is the next one

Return value

A value of the same type as column; default or NULL if there is no row at that offset, for example for the last row of a partition

Examples

SELECT member_name,
	birthday,
	LEAD(member_name) OVER (
		ORDER BY birthday
	) AS next_member,
	LEAD(member_name, 2, 'none') OVER (
		ORDER BY birthday
	) AS two_rows_ahead
FROM FamilyMembers;

SELECT family_member,
	date,
	LEAD(date) OVER (
		PARTITION BY family_member
		ORDER BY date
	) AS next_date,
	LEAD(date) OVER (
		PARTITION BY family_member
		ORDER BY date
	) - date AS until_next
FROM Payments
ORDER BY family_member,
	date;

Details

See also

Where to learn

• Types of window functions