Window functions for beginners: ROW_NUMBER, RANK and running totals

Your manager wants a list of the top earner in each department, with their names. GROUP BY gets you the top salary in each department, then stops: the moment you group, the individual rows, and the names in them, are gone.

Window functions solve exactly this. They calculate across a set of related rows, like GROUP BY does, but they keep every row. This guide covers the three you will use most: ROW_NUMBER, RANK (with its sibling DENSE_RANK) and running totals.

SELECT DepartmentID, MAX(AnnualSalary) AS TopSalary
FROM dbo.Employees
GROUP BY DepartmentID
ORDER BY DepartmentID;

What OVER does

Put OVER (...) after a function and it becomes a window function. The parentheses describe the window: the rows the function may look at for each row. PARTITION BY splits rows into groups, like GROUP BY, but without merging them. ORDER BY inside OVER sets an order within each group, which numbering and running totals need.

SELECT EmployeeID, FirstName, DepartmentID, AnnualSalary,
       MAX(AnnualSalary) OVER (PARTITION BY DepartmentID) AS DeptTopSalary
FROM dbo.Employees
ORDER BY DepartmentID, AnnualSalary DESC;

ROW_NUMBER: number the rows

ROW_NUMBER() gives each row in a partition a number: 1, 2, 3 and so on, in the order you choose. With PARTITION BY, numbering restarts for each group.

SELECT DepartmentID, FirstName, AnnualSalary,
       ROW_NUMBER() OVER (PARTITION BY DepartmentID
                          ORDER BY AnnualSalary DESC, EmployeeID) AS SalaryRank
FROM dbo.Employees
ORDER BY DepartmentID, SalaryRank;

Now the opening question is easy: number the employees in each department by salary, then keep number 1. You cannot write WHERE SalaryRank = 1 in the same query (more on that below), so number them in a CTE first.

WITH Ranked AS (
    SELECT d.DepartmentName, e.FirstName, e.LastName, e.AnnualSalary,
           ROW_NUMBER() OVER (PARTITION BY e.DepartmentID
                              ORDER BY e.AnnualSalary DESC, e.EmployeeID) AS SalaryRank
    FROM dbo.Employees AS e
    INNER JOIN dbo.Departments AS d
        ON d.DepartmentID = e.DepartmentID
)
SELECT DepartmentName, FirstName, LastName, AnnualSalary
FROM Ranked
WHERE SalaryRank = 1
ORDER BY DepartmentName;

RANK and DENSE_RANK: when there are ties

ROW_NUMBER never gives two rows the same number, even when they are tied. That is right for “exactly one per department”, and wrong for a leaderboard where equal salaries deserve equal places. Two functions handle ties differently:

• RANK() gives tied rows the same number, then skips ahead, like a race where two runners share 8th and the next is 10th.
• DENSE_RANK() gives tied rows the same number, then carries on with the next number, so there are no gaps.

SELECT FirstName, LastName, AnnualSalary,
       ROW_NUMBER() OVER (ORDER BY AnnualSalary DESC, EmployeeID) AS RowNum,
       RANK()       OVER (ORDER BY AnnualSalary DESC) AS RankNum,
       DENSE_RANK() OVER (ORDER BY AnnualSalary DESC) AS DenseRankNum
FROM dbo.Employees
ORDER BY AnnualSalary DESC, EmployeeID;

Running totals

A running total is SUM with an order: for each row, add up this row and every row before it. The frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW says exactly that, from the first row up to this one.

WITH Monthly AS (
    SELECT DATEFROMPARTS(YEAR(PaymentDate), MONTH(PaymentDate), 1) AS PaymentMonth,
           SUM(Amount) AS MonthAmount
    FROM dbo.Payments
    WHERE PaymentDate >= '2023-01-01'
      AND PaymentDate <  '2023-07-01'
    GROUP BY DATEFROMPARTS(YEAR(PaymentDate), MONTH(PaymentDate), 1)
)
SELECT PaymentMonth, MonthAmount,
       SUM(MonthAmount) OVER (ORDER BY PaymentMonth
                              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Monthly
ORDER BY PaymentMonth;

Three mistakes to avoid

1. Filtering a window function in WHERE

Window functions are calculated after WHERE, GROUP BY and HAVING, so WHERE cannot see them. WHERE ROW_NUMBER() OVER (...) = 1 stops with error 4108:

Msg 4108, Level 15, State 1
Windowed functions can only appear in the SELECT or ORDER BY clauses.

2. ROW_NUMBER on ties without a tiebreaker

ROW_NUMBER() OVER (ORDER BY AnnualSalary DESC) has to put Daniel and James in some order, and SQL Server is free to choose either. The same query can number them differently on another day or another server, so “keep number 9” is not a stable answer. Add a unique column, such as EmployeeID, to the end of the window’s ORDER BY.

3. Running totals over tied values

With ORDER BY but no frame, SQL Server uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE includes every row with the same ordering value as the current one, so tied rows all get the total for the whole tie.

SELECT PaymentID, PaymentDate, Amount,
       SUM(Amount) OVER (ORDER BY PaymentDate) AS RunningTotal
FROM dbo.Payments
WHERE PaymentDate IN ('2023-01-04', '2023-01-05')
ORDER BY PaymentDate, PaymentID;

SELECT PaymentID, PaymentDate, Amount,
       SUM(Amount) OVER (ORDER BY PaymentDate, PaymentID
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM dbo.Payments
WHERE PaymentDate IN ('2023-01-04', '2023-01-05')
ORDER BY PaymentDate, PaymentID;

Check yourself

WITH Ranked AS (
    SELECT FirstName, LastName, AnnualSalary,
           DENSE_RANK() OVER (ORDER BY AnnualSalary DESC) AS SalaryRank
    FROM dbo.Employees
)
SELECT FirstName, LastName, AnnualSalary
FROM Ranked
WHERE SalaryRank = 2;

WITH Numbered AS (
    SELECT CustomerID, OrderID, OrderDate,
           ROW_NUMBER() OVER (PARTITION BY CustomerID
                              ORDER BY OrderDate, OrderID) AS OrderNumber
    FROM dbo.Orders
)
SELECT CustomerID, OrderID, OrderDate, OrderNumber
FROM Numbered
WHERE CustomerID = 1 AND OrderNumber <= 3
ORDER BY OrderNumber;

Where to go next

Chapter 10 of the SQL Server roadmap covers each function in its own lesson, with practice:

• OVER and PARTITION BY
• ROW_NUMBER
• RANK and DENSE_RANK
• Running totals
• Moving aggregates and window frames: ROWS frames for moving averages
• Top-N results per group: the same pattern, applied to the top two products in each category