MySQL Window Functions with Examples

Window functions preserve row detail

A window function calculates a value using a set of related rows while returning a result for each current row. This distinguishes it from GROUP BY, which collapses rows into one output row per group. Window functions are useful for ranking products within categories, calculating running totals, comparing an event with the previous event, or selecting the latest record per customer. MySQL added window-function support in MySQL 8.0, so ensure the target server supports the syntax.

The OVER clause defines a window. PARTITION BY divides rows into independent groups, while ORDER BY specifies the order used by many calculations. If no partition is specified, the function considers the entire result set. A window's order does not automatically order the final output; add an outer ORDER BY whenever displayed row order matters. Include stable tie-breakers when ranking or numbering so results are deterministic.

SELECT customer_id,
       order_id,
       created_at,
       total,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id
         ORDER BY created_at DESC, order_id DESC
       ) AS order_rank
FROM orders;

ROW_NUMBER assigns a sequential number within each customer partition. The unique order_id breaks timestamp ties. To return only the latest order for each customer, place this query in a CTE or derived table and filter for row number one in an outer query; MySQL does not generally allow filtering a window result in the same query's WHERE because WHERE is evaluated earlier.

Ranking functions and ties

ROW_NUMBER gives every row a distinct sequence number, even when sort values tie. RANK assigns equal ranks to ties and leaves gaps afterward; DENSE_RANK assigns equal ranks without gaps. Choose according to the business meaning. For a competition leaderboard, tied scores may need the same rank; for selecting exactly one row, row_number with a deterministic tie-breaker may be more appropriate. Do not add a tie-breaker to a RANK ordering if it would make equal business scores appear unequal.

Frames and running calculations

Aggregate functions can operate as window functions. A window frame determines which rows in the ordered partition contribute to the current calculation. For a running total, an explicit ROWS frame from unbounded preceding through current row communicates row-by-row accumulation. Default frame behavior can be surprising when order values have peers, so specify the frame when the intended result depends on physical row sequence. Ensure ordering columns define a deterministic order where needed.

SELECT order_id, created_at, total,
       SUM(total) OVER (
         ORDER BY created_at, order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
ORDER BY created_at, order_id;

The window calculates a cumulative amount while retaining each order row. The outer ORDER BY repeats the intended presentation order because window ordering is not a result-order guarantee. For calculations partitioned by customer, include PARTITION BY customer_id so one customer's history does not contribute to another's total.

LAG, LEAD, and comparisons

LAG and LEAD access values from preceding or following rows in a window order. They are useful for comparing an account balance with its previous observation or calculating the interval between consecutive events. Define a stable order and decide what the first or last row should return when there is no neighbor. Handle NULL values deliberately because a missing prior value can mean “no previous record” rather than a zero change.

Performance and query structure

Window operations may sort and process many rows, especially for large partitions. Filter the source set when that matches the required result, and ensure the window order reflects the intended domain. Indexes can help filters and ordering in some plans, but do not assume one index eliminates window work. Use EXPLAIN and realistic benchmarks. Avoid selecting unnecessary columns or computing rankings over an unbounded history when the application only needs a recent range.

Window functions are not always a replacement for grouped aggregates. If the answer needs one row per group, GROUP BY may be simpler. If it needs row details plus a group statistic, a window function is often suitable. For filtering a window result, use an outer query or CTE. Review CTEs, grouping, and execution plans.

Choose the function and frame that match the question, define tie behavior, and specify final ordering separately. Test partitions with one row, tied values, and missing values before relying on analytics in production.