SQL Server 2012 : New Windowing Functions

SQL Server 2012 Introduces 8 New Windowing Functions

 • FIRST_VALUE
 • LAST_VALUE
 • LAG
 • LEAD
 • PERCENT_RANK
 • CUME_DIST
 • PERCENTILE_CONT
 • PERCENTILE_DISC

SQL 2005 Ranking Functions

 • ROW_NUMBER
 • RANK
 • DENSE_RANK
 • NTILE

Traditional Aggregate Functions

 • SUM
 • COUNT
 • MIN
 • MAX
 • AVG

Windowing Functions work with OVER and the optional ORDER BY, PARTITION BY and BETWEEN functions.

SQL Server 2012 lets the aggregate functions work with OVER (ORDER BY ...). Previously only the Ranking Functions worked.


Links

TSQL : Using NTILE to divide a results set up.

SQL 2005 : RANK() vs DENSE_RANK()

Tsql : ROW_NUMBER, RANK, DENSE_RANK and PARTITION BY

SQL Server 2012 Windowing Functions Part 1 of 2: Running and Sliding Aggregates

SQL Server 2012 Windowing Functions Part 2 of 2: New Analytic Functions

SQL Window Functions and You

Tip: OVER and PARTITION BY