Window Functions in Depth

How a window function differs from GROUP BY#

The aggregation-and-grouping page covered how GROUP BY collapses many rows into one summary row per group — useful, but it necessarily loses every other column that doesn't fit into "one value per group." A window function computes a value across a set of related rows too, but without collapsing them — every original row survives in the output, each one annotated with a value computed over its own window of related rows. This is exactly what's needed for something like "this product's price rank within its category," where you still want to see every product, not just one summary row per category.

Every window function shares the same shape: function() OVER (PARTITION BY ... ORDER BY ...).

• PARTITION BY — splits the rows into independent groups, analogous to GROUP BY, but again without collapsing anything.
• ORDER BY (inside OVER) — the order the function processes rows in within each partition, required for ranking and running-total functions.

Window functions are standard SQL (part of the SQL:2003 standard) and work with this same syntax across MySQL (8.0+), PostgreSQL, SQL Server, and most other modern databases — MySQL 5.7 and earlier has no window function support at all, which is worth knowing if a codebase still targets it.

ROW_NUMBER, RANK, and DENSE_RANK#

All three assign each row a position within its partition — they differ only in how ties are handled. Using the products table from earlier in this section:

SELECT name, category, price,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS row_num,
       RANK()       OVER (PARTITION BY category ORDER BY price DESC) AS price_rank,
       DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_dense_rank
FROM products;

With two Electronics products tied at the top price:

name                  category      price   row_num  price_rank  price_dense_rank
Mechanical Keyboard   Electronics   89.00   1        1           1
Gaming Headset        Electronics   89.00   2        1           1
Wireless Mouse        Electronics   24.99   3        3           2
Desk Lamp             Home          19.50   1        1           1

• ROW_NUMBER() never ties — every row gets a unique, strictly increasing number even when values are equal, with ties broken arbitrarily unless the ORDER BY includes a further tiebreaker column.
• RANK() gives tied rows the same rank, then skips the ranks that would have followed — after two rows tied at rank 1, the next distinct price is rank 3, not 2.
• DENSE_RANK() also ties rows together, but never skips — the next distinct price gets the very next integer.

Reach for DENSE_RANK() for "the Nth highest distinct value" with no gaps, RANK() when skipped positions genuinely reflect standing, and ROW_NUMBER() whenever a guaranteed-unique row per position is needed — such as picking exactly one row per group, shown below.

LAG and LEAD#

LAG() and LEAD() read a value from a preceding or following row in the same ordered partition, without a self-join:

SELECT o.id, o.user_id, o.order_date,
       LAG(o.order_date)  OVER (PARTITION BY o.user_id ORDER BY o.order_date) AS previous_order_date,
       LEAD(o.order_date) OVER (PARTITION BY o.user_id ORDER BY o.order_date) AS next_order_date
FROM orders o;

This is the natural tool for sequential comparisons — how long since this customer's last order, whether this month's total is up or down from the previous month — that would otherwise need a self-join on a manually computed row position.

Running totals with SUM() OVER#

The same OVER mechanism applies to ordinary aggregates too — adding ORDER BY inside OVER turns a plain SUM into a running total, since the window then extends from the start of the partition up through the current row by default:

SELECT o.id, o.user_id, o.order_date, o.quantity,
       SUM(o.quantity) OVER (
           PARTITION BY o.user_id
           ORDER BY o.order_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_units_ordered
FROM orders o
ORDER BY o.user_id, o.order_date;

id   user_id   order_date    quantity   running_units_ordered
1    1         2026-01-15    2          2
2    1         2026-02-03    1          3
3    2         2026-02-10    1          1

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the explicit window frame; it's also the implicit default whenever ORDER BY appears inside OVER() with no frame specified, so writing it out is a matter of making the intent unambiguous rather than strictly necessary.

Worked example: top-N per group#

Problem: show the 2 most expensive products in each category — not a global top 2, exactly 2 per category.

A window function can't be filtered directly in WHERE (it's computed after WHERE/GROUP BY have already run), so the standard pattern wraps it in a CTE and filters the outer query:

WITH ranked_products AS (
    SELECT name, category, price,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
    FROM products
)
SELECT name, category, price
FROM ranked_products
WHERE rn <= 2
ORDER BY category, price DESC;

ROW_NUMBER() is the right choice here specifically because it guarantees exactly 2 rows per category even if two products tie in price — RANK() would return 3 rows for a category where the 2nd and 3rd place are tied, since both would be numbered 2.

Common mistakes#

• Trying to filter a window function's result directly in WHERE (WHERE price_rank = 1) — it fails, since window functions are evaluated after WHERE has already run. Wrap the query in a subquery or CTE and filter the outer query instead, as shown above.
• Forgetting PARTITION BY when a per-group result was intended — without it, the function operates across the entire result set as one partition, silently producing a global rank or running total instead of one scoped to each group.
• Reaching for RANK() when ROW_NUMBER() was actually needed (or vice versa) — RANK() can return more than N rows for a "top N per group" query whenever there's a tie at the cutoff, which ROW_NUMBER() never does.
• Omitting ORDER BY inside OVER() on a running-total SUM, silently getting the full partition's total repeated on every row instead of a true row-by-row accumulation.