MySQL Window Functions

MySQL window functions calculate a value using rows related to the current result row without combining those rows into one output row. They are useful for rankings, running totals, comparisons with previous rows, percentages, and moving calculations.

MySQL 8.4 supports aggregate functions used as window functions and specialized functions such as ROW_NUMBER(), RANK(), LAG(), and FIRST_VALUE().

Window Functions and GROUP BY

A grouped query reduces rows into groups. A window function keeps individual result rows and adds a calculation beside each one.

Assume the examples use this table:

Example setup

-- Store sales used by the window-function examples
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    employee VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
);

-- Add rows from two departments
INSERT INTO sales VALUES
    (1, 'Asha', 'North', '2026-01-03', 500.00),
    (2, 'Ravi', 'North', '2026-01-05', 700.00),
    (3, 'Asha', 'North', '2026-01-08', 450.00),
    (4, 'Meera', 'South', '2026-01-04', 800.00),
    (5, 'Kabir', 'South', '2026-01-09', 600.00);

Use the OVER Clause

A function becomes a window function when it is followed by an OVER clause. An empty OVER() uses all query rows as one window.

Example

-- Keep every sale and show the total across all result rows
SELECT
    sale_id,
    employee,
    amount,
    SUM(amount) OVER () AS overall_total
FROM sales
ORDER BY sale_id;

Every row remains in the result, while overall_total contains the same total for the complete window.

Divide Rows with PARTITION BY

PARTITION BY separates result rows into independent partitions. The calculation restarts for each partition.

Example

-- Calculate a total independently for each department
SELECT
    employee,
    department,
    amount,
    SUM(amount) OVER (
        PARTITION BY department
    ) AS department_total
FROM sales
ORDER BY department, sale_id;

PARTITION BY does not collapse rows. It only defines which rows participate in the calculation for the current row.

Order Rows Inside a Window

An ORDER BY inside OVER defines the sequence used by ranking, navigation, and cumulative calculations. It does not determine the final display order of the query.

Example

-- Number sales within each department by amount
SELECT
    employee,
    department,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY department
        ORDER BY amount DESC
    ) AS department_row
FROM sales
ORDER BY department, department_row;

Use the outer ORDER BY when the returned rows must appear in a particular order.

Rank Rows

MySQL provides three commonly used ranking functions.

Function Tie behavior
ROW_NUMBER() Assigns a different sequential number to every row
RANK() Gives peers the same rank and leaves gaps afterward
DENSE_RANK() Gives peers the same rank without leaving gaps

Example

-- Compare the three ranking methods
SELECT
    employee,
    amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_number_value,
    RANK() OVER (ORDER BY amount DESC) AS rank_value,
    DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank_value
FROM sales
ORDER BY amount DESC, sale_id;

Add a stable tie-breaker to the window ordering when deterministic ROW_NUMBER() results are important.

Calculate a Running Total

A running total normally uses an ordered window and an explicit row frame.

Example

-- Add the current amount to all preceding rows
SELECT
    sale_id,
    sale_date,
    amount,
    SUM(amount) OVER (
        ORDER BY sale_date, sale_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM sales
ORDER BY sale_date, sale_id;

The frame begins with the first ordered row and ends at the current row. Including sale_id makes the sequence deterministic when multiple sales have the same date.

Understand Window Frames

A frame narrows an ordered partition to the rows used for the current calculation. MySQL supports ROWS and RANGE frame units.

Frame term Meaning
UNBOUNDED PRECEDING The first row or value in the partition
n PRECEDING A specified number of rows or value units before the current row
CURRENT ROW The current row for ROWS or the current peer group for RANGE
n FOLLOWING A specified number of rows or value units after the current row
UNBOUNDED FOLLOWING The last row or value in the partition

ROWS counts physical rows. RANGE uses values and treats rows with equal ordering values as peers. This distinction can change results when duplicates exist.

Tip: Write an explicit ROWS frame for row-by-row running calculations. Relying on the default frame can unexpectedly include peer rows that share the same ordering value.

Calculate a Moving Average

Example

-- Average the current sale and two preceding sales
SELECT
    sale_id,
    sale_date,
    amount,
    AVG(amount) OVER (
        ORDER BY sale_date, sale_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS three_sale_average
FROM sales
ORDER BY sale_date, sale_id;

Compare with Earlier or Later Rows

LAG() reads an earlier row and LEAD() reads a later row without a self-join.

Example

-- Compare each sale with the preceding sale by the same employee
SELECT
    employee,
    sale_date,
    amount,
    LAG(amount) OVER (
        PARTITION BY employee
        ORDER BY sale_date, sale_id
    ) AS previous_amount,
    amount - LAG(amount) OVER (
        PARTITION BY employee
        ORDER BY sale_date, sale_id
    ) AS change_from_previous
FROM sales
ORDER BY employee, sale_date, sale_id;

The first row in each employee partition has no previous row, so LAG() returns null unless an optional default is supplied.

Use FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE()

Value functions retrieve a value from a position inside the current frame.

Example

-- Compare every sale with the first and final ordered sale
SELECT
    employee,
    sale_date,
    amount,
    FIRST_VALUE(amount) OVER (
        PARTITION BY employee
        ORDER BY sale_date, sale_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY employee
        ORDER BY sale_date, sale_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_amount
FROM sales
ORDER BY employee, sale_date, sale_id;

The explicit full-partition frame is important for LAST_VALUE(). With a frame ending at the current row, its result may simply be the current row's value.

Divide Rows into Buckets

NTILE(n) distributes ordered rows into the requested number of numbered groups as evenly as possible.

Example

-- Divide sales into three amount bands
SELECT
    employee,
    amount,
    NTILE(3) OVER (
        ORDER BY amount DESC
    ) AS amount_band
FROM sales
ORDER BY amount DESC;

When the row count is not evenly divisible by the bucket count, earlier buckets receive one additional row.

Calculate Relative Position

PERCENT_RANK() returns a relative rank from 0 to 1. CUME_DIST() returns the proportion of partition rows less than or equal to the current row according to the window ordering.

Example

-- Show two relative distribution measures
SELECT
    employee,
    amount,
    PERCENT_RANK() OVER (
        ORDER BY amount
    ) AS percent_rank_value,
    CUME_DIST() OVER (
        ORDER BY amount
    ) AS cumulative_distribution
FROM sales
ORDER BY amount;

Reuse a Named Window

The WINDOW clause defines a window once and lets several functions reuse it. It appears after HAVING and before the query's final ORDER BY.

Example

-- Reuse one department ordering for several functions
SELECT
    employee,
    department,
    amount,
    ROW_NUMBER() OVER department_window AS row_number_value,
    RANK() OVER department_window AS rank_value,
    SUM(amount) OVER department_window AS running_total
FROM sales
WINDOW department_window AS (
    PARTITION BY department
    ORDER BY amount DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
ORDER BY department, amount DESC;

Filter Window Results with a Subquery

Window functions are evaluated after WHERE, GROUP BY, and HAVING. Therefore, a window-function alias cannot be filtered directly in the same query's WHERE clause. Calculate it in a subquery or common table expression first.

Example

-- Rank inside the subquery, then filter the generated rank
SELECT
    employee,
    department,
    amount
FROM (
    SELECT
        employee,
        department,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY amount DESC, sale_id
        ) AS department_rank
    FROM sales
) AS ranked_sales
WHERE department_rank <= 2
ORDER BY department, department_rank;

MySQL Window Function Restrictions

  • Window functions can be used in the select list and query ORDER BY, not directly in WHERE, GROUP BY, or HAVING.
  • MySQL does not support nested window functions.
  • Aggregate window functions do not support DISTINCT.
  • Frame endpoints cannot dynamically depend on the current row's column value.
  • Only ROWS and RANGE frames are supported; GROUPS is not.
  • The EXCLUDE frame clause is not supported.
  • Only RESPECT NULLS behavior is supported; IGNORE NULLS produces an error.
  • A single SELECT supports at most 127 distinct windows.

Best Practices

  • Specify a deterministic window order when individual row positions matter.
  • Use PARTITION BY only when calculations must restart by group.
  • Write explicit frames for cumulative, moving, and last-value calculations.
  • Use named windows when several functions share the same definition.
  • Filter generated window values in an outer query.
  • Inspect execution plans and indexes when processing large ordered partitions.

Conclusion

MySQL window functions add calculations across related rows while preserving the detail of each result row. The OVER clause defines partitions, ordering, and frames; ranking, navigation, aggregate, and distribution functions then answer different analytical questions. Explicit ordering and frames are essential for predictable results.

Found This Page Useful? Share It!
Get the Latest Tutorials and Updates
Join us on Telegram