SQL window functions: how every row gets a rank or running total without collapsing the table

Suppose you have a table of sales and you want each sale next to its rank within its region. GROUP BY can't do it: it squashes many rows into one row per group, so the individual sales disappear. A window function does the opposite. It looks at a group of related rows, works something out, and writes the answer onto every row without removing any.

The problem GROUP BY can't solve

Here is the table we will use all the way through. It is called sales and has 8 rows:

Ask for the total per region and you get two rows, one for each region (North 800, South 740). That is useful, but the ids, the reps and the individual amounts are gone. If you want "this sale, and where it stands among its region's sales", you need every row to survive.

The OVER clause: a window onto nearby rows

A window function is any function followed by OVER (...). The brackets describe the window: which other rows each row is allowed to look at. Two parts matter at first:

PARTITION BY region splits the rows into separate groups (called partitions), here one for North and one for South. Each row only sees rows in its own partition. Leave it out and the whole table is one partition.

ORDER BY amount DESC puts the rows of each partition in a line, so that words like "first", "previous" and "so far" mean something.

SELECT id, region, amount,
       RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS r
FROM sales
ORDER BY id;

I ran this in SQLite 3.37 and got 8 rows back, one per sale. The last column was: 3, 1, 1, 4, 1, 3, 1, 4 for ids 1 to 8. Ben's and Ana's 300 sales are both rank 1 in North, because they tie. In South the two 250 sales tie for rank 1, and the 150 sale gets rank 3, not 2: RANK() skips numbers after a tie.

Try it: change the window and watch the column

The table below runs the same 8 rows through a window function. Pick a function, then switch PARTITION BY region on and off. The query on top rewrites itself, and the new column is computed the way a database would compute it.

What you just saw

Turn PARTITION BY off while SUM(amount) is selected. The last row shows 1540, the total of every sale in the table. Turn it back on and the running total restarts at the first South row: the two regions never mix. That restart is the whole point of a partition.

Notice also that the table never changed order or lost a row. The window function only adds a column. That is the difference from GROUP BY, which replaces many rows with one.

A job windows do well: top 2 per group

"Show me each region's two biggest sales" is awkward with GROUP BY alone. With ROW_NUMBER() it is two steps: number the rows inside each region, then keep the numbers 1 and 2.

SELECT * FROM (
  SELECT id, region, rep, amount,
         ROW_NUMBER() OVER (PARTITION BY region
                            ORDER BY amount DESC, id) AS n
  FROM sales
)
WHERE n <= 2
ORDER BY region, n;

The inner query is needed because you can't filter on a window function in the same query's WHERE clause: the database computes windows after it has already filtered rows. Real output from SQLite:

id | region | rep | amount | n
2  | North  | Ben | 300    | 1
3  | North  | Ana | 300    | 2
5  | South  | Dee | 250    | 1
7  | South  | Dee | 250    | 2

Using ROW_NUMBER() here guarantees exactly two rows per region even with ties. With RANK() you could get three rows if three sales tied for second place.

Where this runs, and what to remember

Window functions are part of standard SQL and work in PostgreSQL, MySQL (8.0 and later), SQL Server and SQLite (3.25 and later, released in 2018). If your database is older than that, they may not exist.

Three things to carry into your own queries. First, OVER is what turns an ordinary function into a window function. Second, PARTITION BY chooses which rows each row can see, and ORDER BY inside OVER chooses their order. Third, the window decides the answer only for that column: your SELECT still returns all your rows, in whatever order your final ORDER BY asks for.