SQL Server 2012 introduced Analytic Functions for common window calculations. I collected this series to make them easier to learn.
Before these functions, I often needed longer expressions for the same calculations. Native expressions made those common calculations easier to recognize. Native syntax improves clarity, while any performance advantage still needs measurement.
The eight functions in the original series
| Function | SQLAuthority article | Microsoft reference (2012) |
|---|---|---|
| CUME_DIST | CUME_DIST | CUME_DIST |
| FIRST_VALUE | FIRST_VALUE | FIRST_VALUE |
| LAST_VALUE | LAST_VALUE | LAST_VALUE |
| LEAD | LEAD | LEAD |
| LAG | LAG | LAG |
| PERCENTILE_CONT | PERCENTILE_CONT | PERCENTILE_CONT |
| PERCENTILE_DISC | PERCENTILE_DISC | PERCENTILE_DISC |
| PERCENT_RANK | PERCENT_RANK | PERCENT_RANK |
- CUME_DIST and PERCENT_RANK describe relative position within an ordered partition.
- FIRST_VALUE and LAST_VALUE return values from the applicable window frame.
- LEAD and LAG access another ordered row without a self join for that expression.
- PERCENTILE_CONT interpolates a percentile; PERCENTILE_DISC selects a discrete value.
I review partitioning, ordering, ties, NULL behavior and the frame for each function. LAST_VALUE deserves particular attention. With the default frame, it doesn’t automatically select the final row of the entire partition.
Keep the series history
The original appeared during the 2012 release-candidate period. Its RC0 download reference is historical, not a current installation recommendation. The FIRST_VALUE/LAST_VALUE, LEAD/LAG, and percentile puzzles remain linked below without new spoilers.
This series also brought a memorable surprise: my book sold out and became available again 48 hours later. That happened during the original publication period. I still remember the excitement around those new SQL Server features.
Reference: Analytic function reference.
Related reading
- Historical SQL Server 2012 RC0 resources
- SQL SERVER – Puzzle to Win Print Book – Functions FIRST_VALUE and LAST_VALUE with OVER clause and ORDER BY
- SQL SERVER – Puzzle to Win Print Book – Write T-SQL Self Join Without Using LEAD and LAG
- SQL SERVER – Puzzle to Win Print Book – Explain Value of PERCENTILE_CONT() Using Simple Example
- my book going out of stock and becoming available after 48 hours
Window syntax is not the whole calculation, it is an expression whose ordering and frame still matter.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
7 Comments. Leave new
This page is in my bookmarks now …:)… Great series of SQL Server 2012 analytic functions.
Regards,
P.Anish Shenoy.
Hi pinal sir,
I am expecting this post and here it is.
Thank you for sharing.
Regard$
Chirag Satasiya
Thanks Pinal, I am looking for the exact post…
Appreciate your efforts.
Regards,
Suman Balguri
hi sir i want to know how to count coloumns reapting the same values in table please help me out its attendence table and i want to count number of leave,prestent ,casual leave and all
Post table strucure, sample data with expected result
Hello Pinal,
There is a commercial product which provides hundreds of analytic functions for SQL Server 2005, 2008 and 2012 – financial, statistical, mathematical etc. They are implemented as .NET SQL-CLR functions (permission level:safe), modeled mainly after the analytic functions found in Excel, but also from those found in R, Matlab, and Bloomberg.
It is called XLeratorDB and can be found at westclintech.com
thanks sweete