Window Functions

SQL Server Window Functions calculate an aggregate value based on a group of rows and return multiple rows for each group.

Name Description

CUME_DIST Calculate the cumulative distribution of a value in a set of values

DENSE_RANK Assign a rank value to each row within a partition of a result, with no gaps in

rank values.

FIRST_VALUE Get the value of the first row in an ordered partition of a result set.

LAG Provide access to a row at a given physical offset that comes before the

current row.

LAST_VALUE Get the value of the last row in an ordered partition of a result set.

LEAD Provide access to a row at a given physical offset that follows the current

row.

NTILE Distribute rows of an ordered partition into a number of groups or buckets

PERCENT_RANK Calculate the percent rank of a value in a set of values.

RANK Assign a rank value to each row within a partition of a result set

ROW_NUMBER Assign a unique sequential integer to rows within a partition of a result set,

the first row starts from 1.