MariaDB / Intermediate: concurrency and reusable SQL / Lesson 10

Window functions and ranking

Add per-row rankings and running totals without collapsing detail. Make partition, ordering and frame rules explicit.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Distinguish grouping from analytic windows.
Calculate deterministic running totals and top rows.

Keep the row while calculating context

GROUP BY collapses rows into one row per group. A window function keeps each detail row and calculates across a related partition. MariaDB window functions are available from the 10.2 series, but supported frames and function details must be checked for the target release. PARTITION BY defines independent groups, window ORDER BY defines analytic sequence, and an outer ORDER BY defines displayed sequence. These are separate decisions even when they use the same columns.

Use an explicit row frame

The three synthetic scans have unique identifiers and amounts 5, 8 and 2. An explicit ROWS frame accumulates from the first ordered row through the current one, yielding 5, 13 and 15. SUM(amount) OVER () would instead show the grand total 15 on every row. If timestamps can tie, append a unique event identifier when the business requires an event-by-event sequence. Do not let an accidental peer grouping decide the financial interpretation of a running balance.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT scan_id, amount,
 SUM(amount) OVER (ORDER BY scan_id
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM (SELECT 1 AS scan_id, 5 AS amount
      UNION ALL SELECT 2, 8
      UNION ALL SELECT 3, 2) AS scans
ORDER BY scan_id;

Expected output

scan_id | amount | running
1       | 5      | 5
2       | 8      | 13
3       | 2      | 15

The explicit ROWS frame produces a cumulative value for each retained detail row.

Choose the tie policy before the function

ROW_NUMBER assigns different sequential numbers to tied values, with deterministic assignment only when ordering breaks ties. RANK gives ties the same rank and leaves gaps afterward; DENSE_RANK gives ties the same rank without gaps. For fees 30, 30 and 20 ordered descending, RANK gives 1, 1, 3 and DENSE_RANK gives 1, 1, 2. Selecting the top two rank values therefore answers a different question from selecting exactly two rows.

Filter after calculating the window

A window result is not available to WHERE in the same SELECT stage. Compute it in a derived table or CTE, then filter in the outer query. The example ranks bookings separately for each customer and chooses the highest fee with booking_id as a stable tie-breaker. It requires a training bookings fixture; actual rows are intentionally unspecified. This pattern also makes it clear that rn = 1 chooses exactly one row per nonempty customer partition.

sql Illustrative SQL; expected results are reasoned, not measured.

WITH ranked AS (
 SELECT booking_id, customer_id, fee,
 ROW_NUMBER() OVER (PARTITION BY customer_id
                    ORDER BY fee DESC, booking_id) AS rn
 FROM bookings
)
SELECT booking_id, customer_id, fee FROM ranked
WHERE rn = 1 ORDER BY customer_id;

Filtering is outside the window calculation. An outer ORDER BY still controls output order.

Check boundaries and execution work

LAG reads a preceding row in the defined partition order; its first-row result is normally NULL unless an appropriate default is supplied. That NULL is different from a real zero previous measurement. Window calculations may sort and process many rows even if the final report is small, so inspect plans and bound the source scope. MariaDB supports a subset of standard window features; do not assume MySQL or another database has identical RANGE, NULL ordering or frame-exclusion support.

Common errors and fixes

ERROR 1054: Unknown column rn in WHERE

Why it happens: A window alias was used before its calculation stage.
How to fix it: Wrap the calculation in a CTE or derived table and filter outside.
Prevent it next time: Map query stages when adding analytic filters.

A running total jumps for tied timestamps

Why it happens: Implicit peer/frame behavior did not match an event-by-event requirement.
How to fix it: Use an explicit ROWS frame and a stable unique ordering key.
Prevent it next time: Include ties in the fixture and state their business ordering.

The top-two report contains three rows

Why it happens: A rank-based tie policy was mistaken for a fixed row count.
How to fix it: Choose ROW_NUMBER for exactly two, or document ties with RANK/DENSE_RANK.
Prevent it next time: Specify whether ties are included before choosing a ranking function.

Check your understanding

1. Fees are 50, 50 and 40. Compare RANK <= 2 and ROW_NUMBER <= 2 with a unique tie-breaker.

Show worked solution

Both happen to return two rows in this fixture, but their meanings differ. RANK assigns 1,1,3, preserving tied rank values; ROW_NUMBER assigns 1,2,3, selecting exactly two according to the tie-breaker. DENSE_RANK <= 2 would return all three. Add more equal top fees to expose the difference between rank policy and fixed count.

2. A sensor report uses current_value - LAG(current_value). What should the first row mean?