The snippet library.
Small, practical examples. Search by task or narrow things down below.
Calculate a cumulative integer percentage
SQL Server: Divide a running window sum by the complete total using a unique ordering key.
SQL Server✓ Tested
Divide an ordered result into numbered buckets
SQL Server: NTILE distributes rows across buckets; include a unique tie-breaker for deterministic assignment.
SQL Server✓ Tested
Rank tied scores with competition gaps
SQL Server: RANK leaves a gap after tied rows, unlike DENSE_RANK.
SQL Server✓ Tested
Rank tied scores without gaps
SQL Server: DENSE_RANK assigns equal ranks to ties and increments by one for the next distinct score.
SQL Server✓ Tested
Read the last value of the complete partition
SQL Server: Specify an unbounded-following frame so LAST_VALUE does not stop at the current row.
SQL Server✓ Tested
Read the next row’s value
SQL Server: LEAD reads a later ordered row without a self join and supports a fallback at the end.
SQL Server✓ Tested
Repeat the first ordered value beside each row
SQL Server: FIRST_VALUE reads the first value in the current window frame.
SQL Server✓ Tested
Select the top row in each group with a tie-breaker
SQL Server: Filter an outer query on ROW_NUMBER because window functions are not evaluated in WHERE.
SQL Server✓ Tested
Show a group total beside each detail row
SQL Server: A partitioned aggregate preserves detail rows instead of collapsing them with GROUP BY.
SQL Server✓ Tested
Sum a row and its immediate predecessor
SQL Server: A ROWS frame counts physical ordered rows rather than all peers with the same sort value.
SQL Server✓ Tested