Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 75 ยท Querying with MySQL

Window Functions

Learn MySQL window functions: OVER, PARTITION BY, ROW_NUMBER, RANK, DENSE_RANK, LAG and LEAD, frames, named windows and running totals.

A window function calculates a value for each row using a set of related rows, without collapsing them the way GROUP BY does. You can show each employee's salary next to the department average, number rows inside a group, compare a row with the previous one, or keep a running total, all in a single query and with every original row still present.

MySQL added window functions in version 8.0, so they are unavailable in 5.7 and older. This lesson covers the pieces you will use daily: the OVER clause, partitions, ranking functions, LAG and LEAD, frames and named windows. For the general idea, the SQL window functions lesson is a friendly start.

GROUP BY vs a window

GROUP BY Many rows become one row per group.
Window Every row stays. A calculation is added beside it.

Same aggregates, different shape of result.

1 / 2

The OVER clause

Any aggregate function becomes a window function when you add OVER (...). With an empty OVER (), the window is the whole result. With PARTITION BY, it restarts for every group:

SQL Each salary next to its department average

Press Run (or Ctrl + Enter) to see the result here.

All six rows come back. The company total is 38,500 on every row, and the department average is repeated within each department (Eng 7,667, Sales 5,750, Ops 4,000). A GROUP BY query could not show the individual salary beside those numbers.

The OVER clause can be written as OVER (PARTITION BY ... ORDER BY ... frame). All three parts are optional, but they interact: as soon as you add ORDER BY to an aggregate window, the default frame becomes "from the start of the partition up to the current row" (more on that below), and this is what produces running totals.

Ranking functions

Four functions number rows within a partition according to its ORDER BY:

Function Ties Example for scores 90, 80, 80, 70
ROW_NUMBER() Always unique, ties broken arbitrarily 1, 2, 3, 4
RANK() Ties share a rank, then a gap 1, 2, 2, 4
DENSE_RANK() Ties share a rank, no gap 1, 2, 2, 3
NTILE(n) Splits rows into n buckets for n = 2: 1, 1, 2, 2
SQL Three ways to rank, with ties

Press Run (or Ctrl + Enter) to see the result here.

In Eng, Ben and Cleo both earn 7,000. They share rank 2, so RANK gives Dev a 4 (a gap), DENSE_RANK gives Dev a 3, and ROW_NUMBER simply counts 1 to 4. Because the tie is arbitrary under ROW_NUMBER, add a tie-breaker (ORDER BY salary DESC, name) whenever the numbering must be stable.

The query also introduces a named window: WINDOW w AS (...) defines the specification once and OVER w reuses it. Named windows keep queries short and prevent three copies of the same clause from drifting apart. A named window can also be extended: OVER (w ORDER BY ...) is allowed when w has no ordering yet.

Top N per group

Window functions cannot appear in WHERE, because they are evaluated after filtering. To filter on a rank, compute it in a derived table or CTE and filter outside (CTEs). This is the classic "top two earners per department":

SQL Top two earners per department

Press Run (or Ctrl + Enter) to see the result here.

Eng returns Ava and Ben, Ops returns only Finn (a department with fewer than two people is fine), and Sales returns Eve and Fay. Use RANK instead of ROW_NUMBER if people tied for second place must both be kept.

LAG, LEAD and friends

Value functions reach into neighbouring rows:

  • LAG(expr, n, default) reads the value n rows before the current row (default n is 1).
  • LEAD(expr, n, default) reads n rows after.
  • FIRST_VALUE(expr) and LAST_VALUE(expr) read the first and last row of the frame. NTH_VALUE(expr, n) reads the n-th.

The classic use is a change since the previous period:

SQL Month-over-month change with LAG

Press Run (or Ctrl + Enter) to see the result here.

January has no previous row, so previous and change are NULL. The last row has no next month, so the LEAD default 0 is used. Always give LAG and LEAD an ORDER BY inside OVER: without it "previous" has no meaning.

Frames and running totals

A frame narrows the window to the rows around the current one. It is written as ROWS BETWEEN start AND end, where the bounds are UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING and UNBOUNDED FOLLOWING.

Common frames

Running total UNBOUNDED PRECEDING to CURRENT ROW.
Moving average 2 PRECEDING to CURRENT ROW.
Whole partition UNBOUNDED on both sides.

The frame only matters once the window has an ORDER BY.

1 / 2

The next example computes a running total, a three-month moving average, and the share of the final total, using one named window plus two overrides:

SQL Running total and moving average

Press Run (or Ctrl + Enter) to see the result here.

The running total ends at 7,500. The moving average for the first two months uses fewer rows (a frame never invents rows), so January averages only itself. Note the last column uses the default frame.

ROWS versus RANGE

ROWS counts physical rows. RANGE works on values of the ORDER BY column: all rows with the same ordering value (peers) are included together. When you write ORDER BY without a frame, MySQL uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The surprise: if two rows share the same order value, they get the same running total, because each includes its peers. Choose ROWS explicitly when you want a strict row-by-row total, and remember this default also explains why LAST_VALUE often seems "broken": it returns the current row's value unless you extend the frame to UNBOUNDED FOLLOWING.

Things to know in MySQL

  • Window functions are allowed only in the SELECT list and in ORDER BY. For anything else (filtering, grouping) wrap the query in a derived table or CTE.
  • They run after WHERE, GROUP BY and HAVING, so you can window over aggregates: RANK() OVER (ORDER BY SUM(amount) DESC) in a grouped query is valid.
  • Sorting is the cost. Several windows with the same PARTITION BY ... ORDER BY share one sort, so reuse a named window. An index matching the partition and order columns can avoid the sort (Designing Composite Indexes).
  • Other available functions: CUME_DIST(), PERCENT_RANK(), NTILE(n), and the usual aggregates, including COUNT, MIN, MAX and JSON_ARRAYAGG.
  • DISTINCT is not allowed inside a window aggregate in MySQL: COUNT(DISTINCT x) OVER (...) is an error.

What ran live here

Everything in the examples ran in SQLite, which implements the same window syntax (OVER, PARTITION BY, frames, named windows, LAG, LEAD, rankings). MySQL-specific facts, such as the 8.0 requirement, the sharing of sorts, and the DISTINCT restriction, are described rather than executed.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the main difference between a window function and GROUP BY?

    GROUP BY collapses rows into one per group. A window function adds a calculated value to each existing row.
  2. 2. Scores are 90, 80, 80, 70. What does DENSE_RANK() return for the 70?

    DENSE_RANK has no gaps after ties, so the sequence is 1, 2, 2, 3. RANK would return 4.
  3. 3. How do you keep only the top 3 rows per group using ROW_NUMBER()?

    Window functions are not allowed in WHERE, so compute the rank in an inner query and filter in the outer one.
  4. 4. What does LAG(revenue) return for the first row of a window?

    There is no previous row, so LAG returns NULL unless you pass a default as the third argument.
  5. 5. With ORDER BY but no frame clause, what is the default frame?

    The default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so tied rows share the same running value.

Summary

  • A window function computes per row over related rows, adding a column without collapsing the result. MySQL supports them from 8.0.
  • OVER (PARTITION BY ... ORDER BY ... frame) defines the window. WINDOW w AS (...) names it for reuse.
  • ROW_NUMBER is unique, RANK leaves gaps after ties, DENSE_RANK does not. NTILE(n) makes buckets.
  • LAG/LEAD read neighbouring rows, FIRST_VALUE/LAST_VALUE read the frame edges.
  • Frames (ROWS BETWEEN ...) give running totals and moving averages. The default frame with ORDER BY is RANGE ... CURRENT ROW, which includes tied peers.
  • Filter on a window result by wrapping the query in a CTE or derived table.

Next: Strings, Dates and Math Functions.