Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 39 ยท Joins, CTEs and Window Functions

Window Functions in SQL Server

Use SQL Server window functions: OVER, PARTITION BY, ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, running totals and frame clauses, with live examples.

GROUP BY collapses rows: one row per group. A window function computes something across a set of related rows but keeps every row. That is how you show each employee's salary next to the department average, number rows within a group, rank products by sales, or compare each month with the previous one, all without self joins or correlated subqueries. Window functions arrived in SQL Server 2005 (ranking) and 2012 (running totals, LAG, LEAD), and they are among the most valuable T-SQL skills.

Every window function uses the OVER clause, which defines the "window" of rows:

SQL
function(...) OVER (
    PARTITION BY columns     -- optional: split rows into independent groups
    ORDER BY columns         -- optional or required: order within each partition
    ROWS/RANGE frame         -- optional: which rows around the current row
)

Aggregates as window functions

Any aggregate becomes a window function when you add OVER. With an empty OVER () the window is the whole result:

SQL Each salary next to the department average ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

PARTITION BY department_id restarts the calculation for each department. Unlike GROUP BY, every employee row remains, so you can show details and summaries side by side.

Ranking: ROW_NUMBER, RANK, DENSE_RANK, NTILE

Function Ties Example for values 90, 80, 80, 70
ROW_NUMBER() arbitrary but unique numbers 1, 2, 3, 4
RANK() same rank, then a gap 1, 2, 2, 4
DENSE_RANK() same rank, no gap 1, 2, 2, 3
NTILE(n) splits rows into n buckets e.g. quartiles with NTILE(4)

All of them require ORDER BY inside OVER.

SQL Compare ranking functions ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Top N per group

You cannot filter on a window function in WHERE, because windows are evaluated after WHERE (and after GROUP BY and HAVING). Wrap the query in a CTE or derived table, then filter the rank:

SQL
WITH ranked AS (
    SELECT name, category_id, price,
           ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn
    FROM dbo.products
)
SELECT name, category_id, price FROM ranked WHERE rn <= 2;
SQL The two most expensive products per category ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

This is the same pattern that replaces CROSS APPLY with TOP for top N per group (see APPLY). It also deduplicates: keep rn = 1.

LAG and LEAD

LAG(value, offset, default) reads a value from a previous row, and LEAD from a following row, in the window's order. They replace self joins when comparing consecutive rows:

SQL Month-over-month change ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first row has no previous row, so LAG returns NULL unless you pass a default as the third argument. FIRST_VALUE and LAST_VALUE return the first or last value in the window.

Running totals and frames

Adding ORDER BY to an aggregate turns it into a cumulative one. The frame says exactly which rows count:

SQL
SUM(amount) OVER (ORDER BY ordered_at
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)   -- running total
AVG(amount) OVER (ORDER BY ordered_at
                  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)           -- 3-row moving average

Important T-SQL performance advice: when you specify ORDER BY but no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats ties specially and is much slower (it spools to disk) than ROWS. Always write ROWS BETWEEN ... explicitly for running totals. Also, LAST_VALUE with the default frame returns the current row, a common surprise; use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

SQL Running total of shipping costs ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Percentages of a total

Because the window can span the whole set, share-of-total is one expression:

SQL
SELECT category_id,
       SUM(stock * price) AS stock_value,
       100.0 * SUM(stock * price) / SUM(SUM(stock * price)) OVER () AS percent_of_total
FROM dbo.products
GROUP BY category_id;

Window functions run after grouping, so a window can wrap an aggregate, as SUM(SUM(...)) OVER () does here.

Named windows and performance notes

SQL Server 2022 supports a WINDOW clause so you can name and reuse a definition (OVER w ... WINDOW w AS (PARTITION BY ...)). In any version, an index on the PARTITION BY columns followed by the ORDER BY columns lets SQL Server read data already in order and avoid a costly sort, an idea revisited in the indexing chapter.

Common mistakes

  • Filtering on a window function in WHERE. Use a CTE or subquery.
  • Using RANK when you need unique numbers. Ties share a rank; use ROW_NUMBER with a unique tie-breaker.
  • Leaving out the frame on running totals (slow RANGE default).
  • Forgetting PARTITION BY, which ranks the whole table as one group.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main difference between a window function and GROUP BY?

    A window function adds a calculated column for each row, while GROUP BY returns one row per group.
  2. 2. For salaries 90, 80, 80, 70 in descending order, what does DENSE_RANK return?

    DENSE_RANK gives ties the same rank and does not leave gaps. RANK would give 1, 2, 2, 4.
  3. 3. Why can you not write WHERE ROW_NUMBER() OVER (...) = 1?

    Wrap the query in a CTE or derived table and filter on the computed column in the outer query.
  4. 4. Which frame should you specify for a fast running total?

    ROWS is evaluated in memory; the default RANGE frame is slower because it handles ties and can spill to disk.

Summary

  • OVER (PARTITION BY ... ORDER BY ... frame) defines the window; aggregates become window functions.
  • ROW_NUMBER, RANK, DENSE_RANK and NTILE rank rows; they need ORDER BY.
  • LAG and LEAD compare with neighboring rows; SUM ... OVER builds running totals.
  • Write ROWS BETWEEN ... explicitly; filter ranked rows through a CTE.
  • Window functions run after WHERE, GROUP BY and HAVING.

Next: INSERT, UPDATE and DELETE in T-SQL