PrismitaLEARN · PRACTICE · GROW

What are window functions, and how do they differ from GROUP BY?

SQL Server T-SQL Advanced 3–5 Yrs5+ Yrs SQL Query 1 min read

Quick Answer

A window function computes an aggregate or ranking across a set of rows related to the current row (the “window”, defined by OVER (PARTITION BY ... ORDER BY ...)) without collapsing the rows. GROUP BY returns one row per group; a window function keeps every detail row and adds the computed value as a column.

A window function runs a calculation over a window of rows defined by an OVER clause, but unlike GROUP BY it doesn't merge the rows — you keep every row and get the aggregate or rank as an extra column. So I can show each order alongside that customer's running total, or number the rows within each partition. The pattern I use constantly is ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) in a CTE and then filter to row number 1 to get the most recent order per customer.

  • Ranking: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(n).
  • Offset: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE().
  • Aggregates with a frame: SUM(x) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) for a running total.
  • “Latest row per group”: ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) then filter = 1 in an outer query/CTE.
All SQL Server questions More on T-SQL