PrismitaLEARN · PRACTICE · GROW

What are window functions (ROW_NUMBER, RANK, DENSE_RANK)?

SQL Server T-SQL Intermediate 2–3 Yrs3–5 Yrs SQL Query 1 min read

Quick Answer

Window functions compute a value across a set of rows (“window”) related to the current row, without collapsing them like GROUP BY does. Defined with OVER (PARTITION BY ... ORDER BY ...). ROW_NUMBER gives 1,2,3…; RANK leaves gaps after ties (1,1,3); DENSE_RANK doesn't (1,1,2).

Window functions calculate something across a group of rows related to the current row, but unlike GROUP BY they don't collapse the rows — every row stays and gets an extra computed column. You use OVER (PARTITION BY ... ORDER BY ...). ROW_NUMBER gives a clean 1,2,3 per partition, which is how I get the most recent row per customer or do reliable pagination. RANK and DENSE_RANK differ in how they handle ties — RANK leaves a gap, DENSE_RANK doesn't. And aggregate window functions give running totals. The catch is you can't filter on them in WHERE, so I put the numbering in a CTE and filter outside it.

  • ROW_NUMBER() — unique sequential number per partition. The standard way to get “the latest row per customer” or paginate deterministically.
  • RANK() / DENSE_RANK() — ranking with tie handling; RANK skips numbers after a tie, DENSE_RANK doesn't.
  • Aggregates as windows — SUM(Total) OVER (PARTITION BY CustomerId ORDER BY OrderDate) gives a running total.
  • Top-N per group: number the rows in a CTE, then filter WHERE rn <= 3.
  • They run after WHERE/GROUP BY/HAVING but before ORDER BY, so you can't filter on a window function in WHERE — wrap it in a CTE or subquery.

SQL — latest order per customer 
WITH Ranked AS (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY CustomerId
                              ORDER BY OrderDate DESC) AS rn
    FROM dbo.Orders o
)
SELECT * FROM Ranked WHERE rn = 1;
All SQL Server questions More on T-SQL