Skip to content
Blog
MySQL Window Functions: ROW_NUMBER, RANK and More

Click to use (opens in a new tab)

MySQL Window Functions: ROW_NUMBER, RANK and More

August 14, 2026 by Chat2DBChat2DB Team

Window functions arrived in MySQL 8.0 and changed how analytical queries are written. Before 8.0, ranking rows, computing running totals, or comparing a row with the previous one required correlated subqueries, self-joins, or fragile user-variable tricks. With MySQL window functions, a single SELECT can compute per-group rankings, moving averages, and inter-row deltas while still returning every row of the result set.

This guide walks through the full window function toolkit in MySQL 8.0+: the OVER clause, PARTITION BY, ORDER BY, frame clauses, ranking functions, LAG/LEAD, NTILE, named windows, the top-N-per-group pattern, and the performance characteristics you need to know.

Sample Data

All examples below run against this schema. Create it in any MySQL 8.0+ database (or paste it into a SQL console such as Chat2DB (opens in a new tab), which highlights window function syntax and lets you run each statement individually).

CREATE TABLE sales (
  id INT PRIMARY KEY AUTO_INCREMENT,
  employee VARCHAR(50) NOT NULL,
  region VARCHAR(20) NOT NULL,
  sale_date DATE NOT NULL,
  amount DECIMAL(10,2) NOT NULL
);
 
INSERT INTO sales (employee, region, sale_date, amount) VALUES
('Alice', 'East', '2026-01-05', 1200.00),
('Alice', 'East', '2026-01-12', 800.00),
('Alice', 'East', '2026-02-03', 1500.00),
('Bob',   'East', '2026-01-08', 950.00),
('Bob',   'East', '2026-02-15', 950.00),
('Carol', 'West', '2026-01-10', 2100.00),
('Carol', 'West', '2026-01-25', 700.00),
('Dave',  'West', '2026-02-01', 1800.00),
('Dave',  'West', '2026-02-20', 400.00),
('Erin',  'West', '2026-02-22', 1800.00);

The OVER Clause: How Window Functions Work

A window function computes a value for each row using a "window" of related rows, without collapsing them the way GROUP BY does. The window is defined by the OVER clause:

SELECT
  employee,
  region,
  amount,
  SUM(amount) OVER (PARTITION BY region) AS region_total,
  ROUND(amount / SUM(amount) OVER (PARTITION BY region) * 100, 1) AS pct_of_region
FROM sales;

Expected output (abridged):

employee | region | amount  | region_total | pct_of_region
Alice    | East   | 1200.00 | 3900.00      | 30.8
Alice    | East   |  800.00 | 3900.00      | 20.5
Carol    | West   | 2100.00 | 6800.00      | 30.9
...

Every row is preserved, but each also carries an aggregate computed over its partition. The three components of OVER are:

  • PARTITION BY — splits rows into independent groups (like GROUP BY, but without collapsing rows). Omitting it treats the whole result set as one partition.
  • ORDER BY — defines row order inside each partition, which matters for ranking, LAG/LEAD, and cumulative frames.
  • The frame clause (ROWS BETWEEN ...) — restricts which rows within the partition feed the calculation.

ROW_NUMBER vs RANK vs DENSE_RANK

The three ranking functions differ only in how they treat ties. Bob has two sales of 950.00, and Dave and Erin both have a sale of 1800.00, so ties are easy to observe:

SELECT
  employee,
  amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
  RANK()       OVER (ORDER BY amount DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rnk
FROM sales
WHERE region = 'West';

Expected output:

employee | amount  | row_num | rnk | dense_rnk
Carol    | 2100.00 | 1       | 1   | 1
Dave     | 1800.00 | 2       | 2   | 2
Erin     | 1800.00 | 3       | 2   | 2
Carol    |  700.00 | 4       | 4   | 3
Dave     |  400.00 | 5       | 5   | 4
  • ROW_NUMBER() always produces unique, sequential numbers; ties are broken arbitrarily unless the ORDER BY is deterministic.
  • RANK() gives tied rows the same rank and then skips numbers (1, 2, 2, 4).
  • DENSE_RANK() gives tied rows the same rank without gaps (1, 2, 2, 3).

Use ROW_NUMBER for pagination-style deduplication, RANK when gaps should reflect the number of tied competitors (sports-style ranking), and DENSE_RANK when you want contiguous rank values, for example "the second-highest distinct salary".

Top-N per Group

The most common practical pattern: the top sale per employee, or the top 2 sales per region. Window functions cannot appear in WHERE, so wrap the query in a derived table or CTE:

WITH ranked AS (
  SELECT
    employee,
    region,
    sale_date,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY region
      ORDER BY amount DESC, sale_date ASC
    ) AS rn
  FROM sales
)
SELECT region, employee, sale_date, amount
FROM ranked
WHERE rn <= 2
ORDER BY region, rn;

Expected output:

region | employee | sale_date  | amount
East   | Alice    | 2026-02-03 | 1500.00
East   | Alice    | 2026-01-05 | 1200.00
West   | Carol    | 2026-01-10 | 2100.00
West   | Dave     | 2026-02-01 | 1800.00

Note the tiebreaker (sale_date ASC) in the ORDER BY. Without it, which of the two 1800.00 rows becomes rank 2 is undefined and can change between executions. Always make the ordering deterministic when the row numbers drive filtering.

If ties should all survive, use RANK() instead of ROW_NUMBER() and accept more than N rows per group.

LAG and LEAD: Comparing Adjacent Rows

LAG(expr, offset, default) reads a value from a previous row; LEAD reads from a following row. This is the idiomatic way to compute deltas between consecutive events:

SELECT
  employee,
  sale_date,
  amount,
  LAG(amount, 1, 0) OVER (
    PARTITION BY employee ORDER BY sale_date
  ) AS prev_amount,
  amount - LAG(amount, 1, 0) OVER (
    PARTITION BY employee ORDER BY sale_date
  ) AS change_vs_prev
FROM sales
WHERE employee = 'Alice';

Expected output:

employee | sale_date  | amount  | prev_amount | change_vs_prev
Alice    | 2026-01-05 | 1200.00 |    0.00     | 1200.00
Alice    | 2026-01-12 |  800.00 | 1200.00     | -400.00
Alice    | 2026-02-03 | 1500.00 |  800.00     |  700.00

The third argument is the default when no previous row exists (otherwise you get NULL). Typical uses: day-over-day growth, session gap detection (compare timestamps and flag gaps larger than 30 minutes), and detecting duplicate consecutive readings in sensor data.

Frame Clauses: Running Totals and Moving Averages

When a window function has an ORDER BY inside OVER, MySQL applies a default frame of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That default makes cumulative sums work out of the box:

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
WHERE region = 'East';

For a moving average over a fixed number of rows, specify a ROWS frame explicitly:

SELECT
  sale_date,
  amount,
  ROUND(AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ), 2) AS moving_avg_3
FROM sales
WHERE region = 'West';

Expected output:

sale_date  | amount  | moving_avg_3
2026-01-10 | 2100.00 | 2100.00
2026-01-25 |  700.00 | 1400.00
2026-02-01 | 1800.00 | 1533.33
2026-02-20 |  400.00 |  966.67
2026-02-22 | 1800.00 | 1333.33

Two subtleties worth memorizing:

  • ROWS counts physical rows; RANGE groups peer rows with equal ORDER BY values. With duplicate dates, RANGE ... CURRENT ROW includes all rows sharing the current date, which can make a "running total" jump in steps. Use ROWS when you want strictly row-by-row accumulation.
  • MySQL supports ROWS and RANGE frames but not the GROUPS frame unit, and RANGE with a numeric offset (e.g. RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW) is not supported the way PostgreSQL 11+ supports it. In MySQL, RANGE offsets are limited; for date-window logic, pre-bucket the data or use a self-join.

NTILE: Bucketing Rows

NTILE(n) distributes ordered rows into n roughly equal buckets — useful for quartiles or percentile bands:

SELECT
  employee,
  amount,
  NTILE(4) OVER (ORDER BY amount DESC) AS quartile
FROM sales;

With 10 rows and 4 buckets, MySQL assigns bucket sizes 3, 3, 2, 2 (earlier buckets get the extra rows). Quartile 1 contains the top three sale amounts.

Named Windows with the WINDOW Clause

When several functions share the same window definition, repeat yourself less with a named window:

SELECT
  employee,
  region,
  amount,
  ROW_NUMBER() OVER w AS rn,
  RANK()       OVER w AS rnk,
  SUM(amount)  OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY amount DESC)
ORDER BY region, rn;

The WINDOW clause sits between HAVING and ORDER BY. A named window can be extended inline — here the third function adds a frame clause on top of w. This keeps complex analytical queries readable and guarantees all functions use an identical partition/order definition.

Differences and Limits vs PostgreSQL

If you move between engines, note where MySQL's implementation is narrower:

  • No FILTER clause. PostgreSQL allows COUNT(*) FILTER (WHERE ...) OVER (...); in MySQL use SUM(CASE WHEN ... THEN 1 ELSE 0 END) OVER (...) instead.
  • No GROUPS frame unit and no EXCLUDE frame options (EXCLUDE CURRENT ROW, EXCLUDE TIES).
  • Limited RANGE offsets. PostgreSQL supports RANGE BETWEEN '7 days' PRECEDING ...; MySQL supports RANGE mainly with UNBOUNDED/CURRENT ROW endpoints and numeric offsets only for numeric/temporal ORDER BY in constrained forms.
  • No user-defined aggregates as window functions and no WITHIN GROUP ordered-set aggregates (percentile_cont etc.). Emulate percentiles with NTILE, PERCENT_RANK(), or CUME_DIST().
  • IGNORE NULLS is not supported for LAG/LEAD/FIRST_VALUE/LAST_VALUE. A common workaround is a two-step query that carries the last non-NULL value forward using MAX over a derived grouping column.

Everything shown above — ranking functions, LAG/LEAD, NTILE, frames, named windows — behaves consistently across MySQL 8.0+ and PostgreSQL, so the core patterns are portable.

Performance Tips

Window functions are evaluated after WHERE, GROUP BY, and HAVING, and each distinct window definition may require its own sort of the intermediate result. Practical guidance:

  • Index to match PARTITION BY + ORDER BY. For OVER (PARTITION BY region ORDER BY sale_date), a composite index on (region, sale_date) lets the optimizer read rows already in window order and skip the filesort. Check EXPLAIN FORMAT=TREE: you want to avoid a Sort node feeding the Window step when the table is large.
  • Filter early. Push WHERE predicates down before the window computation. Windowing 100k rows and discarding 99k afterwards wastes the sort; if the filter does not depend on the window value, apply it in the innermost query block.
  • Reuse window definitions. Functions sharing the same WINDOW definition (or identical OVER clauses) can share one sort pass. Ten functions over ten different orderings means up to ten sorts.
  • Watch windowing_use_high_precision. MySQL disables some fast-path aggregation for moving frames when high precision is on (the default) for DOUBLE types; with fixed frames on large partitions, SUM/AVG recomputation cost matters. Prefer DECIMAL where exactness is required anyway.
  • Beware of large partitions with NTILE/PERCENT_RANK. They need the full partition materialized before producing the first row, so memory (or on-disk temporary tables, visible via Created_tmp_disk_tables) becomes the constraint.

When tuning, run EXPLAIN ANALYZE on the real query — it reports actual time spent in the Window aggregate and Sort iterators, which tells you immediately whether an index change removed the sort.

FAQ

Which MySQL version supports window functions? MySQL 8.0 and later. MySQL 5.7 has no window functions; the old user-variable emulation (@rownum := @rownum + 1) is unreliable and officially deprecated behavior.

Can I use a window function in WHERE or HAVING? No. Window functions are computed after those clauses. Wrap the query in a CTE or derived table and filter in the outer query, as in the top-N example above.

ROW_NUMBER or RANK for deduplication? ROW_NUMBER — you want exactly one row kept per partition (rn = 1), and RANK can return several rows tied at 1.

Do window functions replace GROUP BY? No. GROUP BY collapses rows into one per group; window functions keep every row. They compose well: you can apply a window function over the output of a GROUP BY in the same query, e.g. ranking monthly totals after aggregating by month.

AWS Cron Expressions: EventBridge and Scheduler SyntaxUser-Defined Functions in SQL: CREATE FUNCTION Guide