MySQL / Reliable applications and analytical queries / Lesson 11

Window functions and analytical frames

Calculate ranks and running values while retaining individual order rows.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Distinguish grouping from window calculations.
Specify deterministic ranking, partitions, and explicit frames.

Keep detail while computing context

Use the unchanged three-order fixture. GROUP BY collapses rows into groups, while a window function computes over related rows and retains each input row. OVER defines that related set. PARTITION BY restarts a calculation for each customer; without it, all qualifying rows form one partition. WHERE filtering happens before window evaluation, so filtering to paid orders changes the analytical population.

Rank with explicit ties

ROW_NUMBER assigns a sequence, RANK leaves gaps after ties, and DENSE_RANK does not. If ROW_NUMBER orders only by a nonunique timestamp, tied rows have no stable assignment. Add a unique id when one winner is required. Adding that id to RANK also removes timestamp ties, which may change the business meaning; tie handling must match the question rather than follow one universal recipe.

sql Latest order for each ordering customer

WITH ranked AS (
 SELECT id,customer_id,total,
 ROW_NUMBER() OVER (PARTITION BY customer_id
                    ORDER BY placed_at DESC,id DESC) AS rn
 FROM orders
)
SELECT id,customer_id,total FROM ranked WHERE rn=1 ORDER BY customer_id;

Expected output

id | customer_id | total
102 | 1 | 30.00
103 | 2 | 10.00

Customer 1's latest order is 102 with total 30.00; customer 2's is 103 with total 10.00. Noor has no input order and therefore no partition. To retain Noor, join this result to customers using a left join. The outer query filters the already computed row number.

A partition differs from a frame

A frame selects rows relative to the current row within a partition. With window ORDER BY, the default frame is RANGE from the partition start through the current peer group. Equal ordering values are peers and can enter a running SUM together. An explicit ROWS frame with a unique ordering sequence provides a row-by-row cumulative result. Frame choice especially matters for LAST_VALUE, which otherwise often returns the current-frame value.

sql A deliberate running total

SELECT id,customer_id,total,
 SUM(total) OVER (PARTITION BY customer_id ORDER BY placed_at,id
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders ORDER BY customer_id,placed_at,id;

Expected output

id | customer_id | total | running_total
101 | 1 | 25.00 | 25.00
102 | 1 | 30.00 | 55.00
103 | 2 | 10.00 | 10.00

Amina's first row is 25.00 and second is 55.00; Leo's separate partition starts at 10.00. The window ORDER BY defines calculation sequence, while the final ORDER BY defines presentation. The two are written separately because a window does not guarantee output order.

Filter in an outer query

Window results cannot be used directly in WHERE at the same query level, and MySQL 8.4 does not provide a QUALIFY clause. Use a CTE or derived table, as the ranking example does. An index may help access or ordering but does not eliminate every sort or temporary structure. Inspect the plan and memory behavior on representative scale; analytical elegance alone is not evidence of low execution cost.

Common errors and fixes

ERROR 3593 (HY000): You cannot use the window function 'row_number' in this context.

Why it happens: ROW_NUMBER() was placed directly in WHERE.
How to fix it: Calculate it in a CTE and filter the outer SELECT.
Prevent it next time: Separate input population from post-window selection.

Running totals jump across equal timestamps.

Why it happens: The default RANGE frame includes peers together.
How to fix it: Use an explicit ROWS frame and justified unique ordering for rowwise accumulation.
Prevent it next time: Specify frame semantics whenever cumulative results matter.

The latest order changes between runs.

Why it happens: ROW_NUMBER used a nonunique ordering without a tie breaker.
How to fix it: Add id DESC after the intended timestamp order.
Prevent it next time: Define how simultaneous events should be resolved in the report contract.

Check your understanding

1. Compute the difference from the previous order for each customer.

Show worked solution

Use total - LAG(total) OVER (PARTITION BY customer_id ORDER BY placed_at,id). Order 101 yields NULL, 102 yields 5.00, and 103 yields NULL. Those NULL values mean no preceding order in their partitions, not an arithmetic failure or a zero change.

2. How does filtering paid orders first change Amina's running total?