SQL Fundamentals: Joins, NULL, Aggregate Functions, and Subqueries

Part 15 of 18 · ~3 min

Window Functions for Ranking and Running Totals

Slide three's last piece is a pay ladder — where every employee stands, salary-wise, against their own department, with nobody's row disappearing the way GROUP BY would make it disappear. A window function is built for exactly that trade-off: it looks across a whole set of related rows to compute something, the same raw material GROUP BY works with, but instead of folding those rows down to a summary, it hands each original row back untouched, with one new value bolted onto the end.

SELECT name, dept, salary,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS pay_rank
FROM Employees;

name  | dept  | salary | pay_rank
------|-------|--------|----------
Eve   | HR    | 55000  | 1
Carol | IT    | 70000  | 1
Dave  | IT    | NULL   | 2
Bob   | Sales | 60000  | 1
Alice | Sales | 50000  | 2

PARTITION BY is doing the bucketing — one bucket per department, same idea as GROUP BY. OVER is the part that changes the outcome: instead of the engine handing back one row per bucket, every original row survives, each one now carrying its rank inside its own bucket. Look closely at where Dave landed, too — last place in IT, rank 2, despite the sort being DESC. That's the sorting rule from several chapters ago showing up somewhere concrete: SQLite files NULL under "smaller than anything real," which puts it at the front when you sort ascending and at the back when you sort descending. Rank 2 isn't SQL judging Dave — it's simply where a value with no defined size has to land once every other value has been placed.

The three ranking functions only disagree about one thing: what happens when two rows tie. Give RANK() a tie and the next rank jumps ahead to account for it — first place, first place, then third, skipping second entirely. DENSE_RANK() refuses to leave that gap, so the same tie produces first, first, second. ROW_NUMBER() doesn't acknowledge ties as a concept at all — it hands out a strictly increasing number to every row, breaking any tie by some arbitrary internal order, so two people can never legitimately share a number.

A "total payroll spend so far, in employee-ID order" running-total chart needs nothing new mechanically — drop RANK() out of that same window-function shape and put an ordinary aggregate like SUM() in its place, and the machinery underneath does the rest:

SELECT id, name, salary,
       SUM(salary) OVER (ORDER BY id) AS running_total
FROM Employees;
-- Dave's own row adds nothing to the running total (his salary is unknown, not zero),
-- but the total carries forward unchanged rather than resetting or erroring

There's also a cleaner way to get "just the top earner per department" than the correlated MAX() subquery from a few chapters back — one that doesn't conceptually re-run anything per row. Number every row within its department by salary, then keep only the ones numbered first:

SELECT name, dept, salary FROM (
  SELECT name, dept, salary,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM Employees
) ranked
WHERE rn = 1;
-- one row per department: the top earner, computed without a self-referencing subquery per outer row