LEAD

Parameters

column

Column or expression whose value is taken from the next row

offsetINToptional

How many rows ahead to look, a non-negative integer, 1 by default

defaultoptional

Value 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; without it the order is undefined

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 room_id,
	start_date,
	end_date,
	DATEDIFF(
		LEAD(start_date) OVER (
			PARTITION BY room_id
			ORDER BY start_date
		),
		end_date
	) AS free_days_after
FROM Reservations;

Details

See also

Where to learn

• Types of window functions