Window functions in MySQL 8

MySQL gained window functions in 8.0. A window function computes a value for each row from a set of related rows (the window) while keeping every row in the output, which GROUP BY cannot do.

function(...) OVER (PARTITION BY ... ORDER BY ... frame)

PARTITION BY splits the rows into independent groups, ORDER BY orders rows within each group, and the frame (covered below) chooses which rows of the group an aggregate sees.

Ranking and neighbours

1CREATE TABLE scores (player VARCHAR(10), team VARCHAR(5), points INT);2INSERT INTO scores VALUES3  ('ann', 'red', 50), ('bob', 'red', 70), ('cy', 'red', 70),4  ('dee', 'blue', 40), ('eve', 'blue', 90);

1SELECT team, player, points,2       ROW_NUMBER() OVER (PARTITION BY team ORDER BY points DESC, player) AS rn,3       RANK()       OVER w AS rnk,4       DENSE_RANK() OVER w AS dense,5       LAG(points)  OVER (PARTITION BY team ORDER BY points DESC, player) AS prev_points,6       points - FIRST_VALUE(points) OVER w AS vs_leader7FROM scores8WINDOW w AS (PARTITION BY team ORDER BY points DESC)9ORDER BY team, rn;

• ROW_NUMBER() numbers rows 1, 2, 3 with no ties. When two rows tie on the ORDER BY, which gets 1 is not defined unless you add a tiebreaker; here player is that tiebreaker.
• RANK() gives ties the same rank and then skips: 1, 1, 3.
• DENSE_RANK() gives ties the same rank without gaps: 1, 1, 2.
• LAG(x) and LEAD(x) read the previous or next row's value, NULL at the edge; FIRST_VALUE(x) reads the first row of the frame.

The WINDOW w AS (...) clause names a window definition so several functions can share it.

The default frame

An aggregate such as SUM with an ORDER BY in its window uses the frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW unless you say otherwise. RANGE counts peers, rows with the same ORDER BY value, as part of the current row.

1CREATE TABLE payments (id INT PRIMARY KEY, paid_on DATE, amount INT);2INSERT INTO payments VALUES3  (1, '2026-03-01', 10), (2, '2026-03-02', 20),4  (3, '2026-03-02', 30), (4, '2026-03-05', 5);

1SELECT id, paid_on, amount,2       SUM(amount) OVER (ORDER BY paid_on) AS default_frame,3       SUM(amount) OVER (ORDER BY paid_on, id4                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame,5       LAST_VALUE(amount) OVER (ORDER BY paid_on, id) AS last_default,6       LAST_VALUE(amount) OVER (ORDER BY paid_on, id7                         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_whole8FROM payments9ORDER BY id;

The two payments on 2 March are peers, so the default frame gives both of them the total through the end of that day, 60. With ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and a unique ordering (paid_on, id), the running total grows one row at a time. The same frame rule is why LAST_VALUE(x) with only an ORDER BY returns the current row's value rather than the last row of the partition: you must widen the frame to UNBOUNDED FOLLOWING. A frame can also have a bounded start: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is the current row and the two before it in the window's order, the frame of a moving average.

Filtering on a window function

Window functions are evaluated after WHERE, GROUP BY and HAVING, so none of those clauses can refer to them, not even through an alias.

1CREATE TABLE scores (player VARCHAR(10), team VARCHAR(5), points INT);2INSERT INTO scores VALUES3  ('ann', 'red', 50), ('bob', 'red', 70), ('cy', 'red', 70),4  ('dee', 'blue', 40), ('eve', 'blue', 90);5-- A helper that runs a statement and records the error instead of stopping.6CREATE TABLE attempts (n INT AUTO_INCREMENT PRIMARY KEY, statement TEXT, outcome TEXT);7CREATE PROCEDURE try_sql(IN stmt TEXT)8BEGIN9  DECLARE EXIT HANDLER FOR SQLEXCEPTION10  BEGIN11    GET DIAGNOSTICS CONDITION 1 @err_no = MYSQL_ERRNO, @err_msg = MESSAGE_TEXT;12    INSERT INTO attempts (statement, outcome)13    VALUES (stmt, CONCAT('error ', @err_no, ': ', REPLACE(@err_msg, CONCAT(DATABASE(), '.'), '')));14  END;15  SET @sql = stmt;16  PREPARE s FROM @sql;17  EXECUTE s;18  DEALLOCATE PREPARE s;19  INSERT INTO attempts (statement, outcome) VALUES (stmt, 'ok');20END;

1CALL try_sql('SELECT player FROM scores WHERE RANK() OVER (ORDER BY points DESC) = 1');2CALL try_sql('SELECT player, RANK() OVER (ORDER BY points DESC) AS r FROM scores HAVING r = 1');3CALL try_sql('WITH ranked AS (SELECT player, RANK() OVER (ORDER BY points DESC) AS r FROM scores) SELECT player FROM ranked WHERE r = 1');4 5SELECT n, outcome FROM attempts ORDER BY n;

Compute the window in a CTE or derived table and filter outside it, as the third attempt does. That is also the standard shape for "the latest row per group", and for the exercise, which filters on a moving average.

Common mistakes

• ROW_NUMBER() without a tiebreaker. Two rows with equal sort values can swap numbers between runs; always end the ORDER BY with a unique column.
• Running totals with the default frame on non-unique ordering. Peers share one total, as the 60 / 60 rows show.
• WHERE rank_col = 1 in the same query. Error 3593 or 3594; wrap it.
• The "join back to MAX(date)" approach for the latest row. It returns two rows when two orders share the latest date.

What interviewers ask

• Difference between RANK and DENSE_RANK? Both share a rank on ties; RANK leaves a gap after them, DENSE_RANK does not.
• Why does my running total jump on rows with the same date? The default RANGE frame includes peers; use ROWS and a unique ordering.
• How do you get each customer's latest order? ROW_NUMBER() partitioned by customer, ordered by date descending plus a tiebreaker, then filter = 1 outside.