LAG

Parameters

column

Column or expression whose value is taken from the previous row

offsetintegeroptional

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

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 previous 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 first row of a partition

Examples

SELECT member_name,
	birthday,
	LAG(member_name) OVER (
		ORDER BY birthday
	) AS previous_member,
	LAG(member_name, 2, 'none') OVER (
		ORDER BY birthday
	) AS two_rows_back
FROM FamilyMembers;

SELECT family_member,
	date,
	LAG(date) OVER (
		PARTITION BY family_member
		ORDER BY date
	) AS previous_date,
	date - LAG(date) OVER (
		PARTITION BY family_member
		ORDER BY date
	) AS since_previous
FROM Payments
ORDER BY family_member,
	date;

Details

See also

Where to learn

• Types of window functions