MySQL Window Functions — ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE

You already know GROUP BY — it smashes rows together and you lose detail. Window functions do the opposite: they let you run calculations across rows without collapsing them. Every row stays, and you get an extra column with the result.

Only works in MySQL 8.0+. Nothing before that supports window functions.

What you'll learn

• The OVER() Clause — the engine behind every window function
• ROW_NUMBER, RANK, DENSE_RANK — numbering rows, with and without ties
• NTILE — splitting rows into buckets
• LAG and LEAD — peeking at rows before or after the current one
• FIRST_VALUE and LAST_VALUE — grabbing endpoints of a window

Setup

Run this once and you're good for every example in the tutorial:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary INT
);

INSERT INTO employees VALUES
(1, 'Alice',   'Engineering', 90000),
(2, 'Bob',     'Engineering', 75000),
(3, 'Charlie', 'Engineering', 75000),
(4, 'Diana',   'Marketing',   65000),
(5, 'Eve',     'Marketing',   72000),
(6, 'Frank',   'Marketing',   60000),
(7, 'Grace',   'Sales',       80000),
(8, 'Henry',   'Sales',       85000),
(9, 'Ivy',     'Sales',       80000);

SELECT * FROM employees;

The OVER() Clause

Every window function hangs off OVER(). Think of it as the instruction that says "hey, here's the window I want to work with."

function_name(args) OVER (
    PARTITION BY col   -- split into groups (optional)
    ORDER BY col       -- order within each group (optional)
    frame_clause       -- pick which rows to include (optional)
)

Three knobs you can turn:

• PARTITION BY — chops rows into groups, same way GROUP BY does, except it doesn't mash them together. Leave it out and the whole result set is one big partition.
• ORDER BY — decides the order of rows inside each partition. Ranking functions require this.
• Frame clause — narrows the window to a specific set of rows (like "only the last 3 rows"). We'll get to this later.

Named windows save typing

If you're slapping the same window on multiple columns, define it once with WINDOW:

SELECT
    name, salary,
    ROW_NUMBER() OVER w AS row_num,
    RANK() OVER w AS rnk,
    DENSE_RANK() OVER w AS dense_rnk
FROM employees
WINDOW w AS (ORDER BY salary DESC);

Cleaner, and if you need to change the order, you change it in one place.

ROW_NUMBER, RANK, DENSE_RANK

These three assign numbers to rows. They look alike but treat ties differently. This trips people up constantly, so pay attention to the behavior more than the syntax.

ROW_NUMBER

Every row gets a unique number. If two rows have the same salary, MySQL picks an order for you — you don't control which one gets 3 vs 4.

SELECT
    name, department, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

Notice Grace and Ivy both earn 80k but got different numbers. That's ROW_NUMBER — no ties allowed.

RANK

Ties get the same rank, but a gap appears after them. Two people tied for 1st? The next rank is 3.

SELECT
    name, department, salary,
    RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees;

See the jump from 3 to 5? That's the gap. Useful if you're building a leaderboard and want to show "there are actually two people ahead of you."

DENSE_RANK

Same tie handling as RANK, but no gaps. Tied for 1st together? Next person is rank 2, not 3.

SELECT
    name, department, salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

Side-by-side

The gap at rank 4 tells you RANK was used. DENSE_RANK never skips numbers.

When you'd use each

• ROW_NUMBER — picking exactly N rows per group (like "top 3 per department"), pagination, removing duplicates.
• RANK — competitions, olympic-style scoring where gaps matter.
• DENSE_RANK — "show me all distinct salary tiers" or when you want compact groupings.

Ranking inside departments

Add PARTITION BY and the numbering resets per group:

SELECT
    name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Each department restarts at 1. This is probably what you'll use ROW_NUMBER for most of the time.

NTILE

NTILE(n) divides rows into n buckets as evenly as possible and tells you which bucket each row landed in.

NTILE(n) OVER (ORDER BY column)

SELECT
    name, salary,
    NTILE(3) OVER (ORDER BY salary DESC) AS bucket
FROM employees;

9 rows split into 3 buckets = perfectly even at 3 rows each.

When counts don't divide evenly, the first buckets get the extra rows. 10 rows into 3 buckets gives you 4 + 3 + 3.

Why you'd use it: quartiles, deciles, splitting a customer list into N equal groups for A/B testing, assigning work batches.

Per-department version works the same way:

SELECT
    name, department, salary,
    NTILE(3) OVER (PARTITION BY department ORDER BY salary DESC) AS dept_bucket
FROM employees;

LAG and LEAD

These two let you reach into the row before or after the current one without writing a self-join. Incredibly handy for comparisons.

LAG

Grabs a value from the row above.

LAG(column, offset, default) OVER (ORDER BY column)

• offset — how many rows back (default 1).
• default — what to return when there's no previous row (default NULL).

SELECT
    name, salary,
    LAG(salary) OVER (ORDER BY salary) AS previous_salary,
    salary - LAG(salary) OVER (ORDER BY salary) AS difference
FROM employees;

This is how you'd calculate "how much did we grow compared to last month?" — pull previous month's revenue with LAG and subtract.

LEAD

Same idea, but looks at the next row instead.

LEAD(column, offset, default) OVER (ORDER BY column)

SELECT
    name, salary,
    LEAD(salary) OVER (ORDER BY salary) AS next_salary,
    LEAD(salary) OVER (ORDER BY salary) - salary AS gap
FROM employees;

Real examples:

• LAG — month-over-month revenue, week-over-week traffic, comparing current value to previous.
• LEAD — finding out if a session is the user's last before they churned, or checking "what's the next event in this sequence."

Per-department comparison:

SELECT
    name, department, salary,
    LAG(salary) OVER (PARTITION BY department ORDER BY salary) AS prev_dept_salary
FROM employees;

FIRST_VALUE and LAST_VALUE

These grab the first or last row in a window. Sounds simple, but LAST_VALUE has a trap that'll bite you if you don't know about it.

The frame clause

OVER (ORDER BY col) by itself only sees rows from the start up to the current row. That's the default frame: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Fine for FIRST_VALUE — the first row stays the same regardless. But LAST_VALUE will just return the current row, which is useless.

To fix this, you explicitly say "look at the whole partition":

OVER (ORDER BY col ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

FIRST_VALUE

Returns the first value in the window. Works fine with the default frame.

SELECT
    name, salary,
    FIRST_VALUE(name) OVER (ORDER BY salary) AS lowest_paid
FROM employees;

Lowest-paid person shows up on every row. Useful for benchmarks like "what's the minimum salary in this department?"

Per-department:

SELECT
    name, department, salary,
    FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary) AS dept_lowest
FROM employees;

LAST_VALUE — watch the frame

Same idea, but you must override the default frame:

SELECT
    name, salary,
    LAST_VALUE(name) OVER (
        ORDER BY salary
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS highest_paid
FROM employees;

Now it works — Alice (highest salary) shows on every row.

Without the right frame, here's what you get:

SELECT
    name, salary,
    LAST_VALUE(name) OVER (ORDER BY salary) AS wrong
FROM employees;

Every row shows its own name. Not helpful. Always spell out the frame for LAST_VALUE.

Frame options at a glance

{ROWS | RANGE | GROUPS} BETWEEN
    {UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW}
AND
    {UNBOUNDED FOLLOWING | n FOLLOWING | CURRENT ROW}

• ROWS — counts literal rows.
• RANGE — counts rows whose values fall within a range of the current row.
• GROUPS (MySQL 8.0+) — counts groups of rows that share the same ORDER BY value.

Rules that apply to all of them

• Window functions run after WHERE, GROUP BY, and HAVING. If a row is filtered out, it's not part of any window.
• No PARTITION BY means the entire result set is one window.
• ORDER BY inside OVER() controls the order within the window, independent of your query's final ORDER BY.
• You can't use window functions in WHERE — wrap it in a subquery or CTE if you need to filter on the result.

MySQL-specific gotchas

• Requires MySQL 8.0+. MariaDB has its own implementation (10.2+), syntax may differ slightly.
• Window functions + WITH ROLLUP = error. They don't work together.
• Named windows (WINDOW w AS ...) can only reference names defined earlier in the same SELECT.
• Performance: window functions use memory or temp tables. If you're partitioning millions of rows, test with real data — it's not always faster than a well-written join or subquery.