LAG()

Accesses data from a previous row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.

LEAD()

Accesses data from a subsequent row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.

Common Syntax

LAG | LEAD
( expression )
OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list ] )

FIRST_VALUE()

Returns the first value in an ordered set of values in SQL Server 2019

LAST_VALUE()

Returns the last value in an ordered set of values in SQL Server 2019 (15.x).

Common Syntax

FIRST_VALUE | LAST_VALUE
( expression ) OVER
( [ PARTITION BY expr_list ] [ ORDER BY order_list ][ frame_clause ] )