MySQL Window Functions

Window functions in MySQL perform calculations across a set of rows that are related to the current row, without collapsing the result into groups. Unlike aggregate functions with GROUP BY, window functions return a value for every row while still having access to values from other rows in the defined window.

Window Functions vs Aggregate Functions

Syntax

function_name() OVER (
    PARTITION BY column
    ORDER BY column
    ROWS/RANGE frame_specification
)

• PARTITION BY — Divides rows into groups (like GROUP BY but without collapsing)
• ORDER BY — Defines the order of rows within the window
• ROWS/RANGE — Defines the frame (subset of the window) for calculation

Sample Table

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    salesperson VARCHAR(100),
    region VARCHAR(50),
    amount DECIMAL(10,2)
);

INSERT INTO sales VALUES (1, 'Arjun', 'North', 15000);
INSERT INTO sales VALUES (2, 'Priya', 'South', 22000);
INSERT INTO sales VALUES (3, 'Arjun', 'North', 18000);
INSERT INTO sales VALUES (4, 'Meena', 'East',   9500);
INSERT INTO sales VALUES (5, 'Priya', 'South', 31000);
INSERT INTO sales VALUES (6, 'Meena', 'East',  12000);

ROW_NUMBER()

Assigns a unique sequential number to each row within the partition.

SELECT salesperson, region, amount,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_num
FROM sales;

Result:

salesperson | region | amount   | row_num
------------+--------+----------+--------
Arjun       | North  | 18000.00 | 1
Arjun       | North  | 15000.00 | 2
Meena       | East   | 12000.00 | 1
Meena       | East   |  9500.00 | 2
Priya       | South  | 31000.00 | 1
Priya       | South  | 22000.00 | 2

RANK() and DENSE_RANK()

RANK() assigns the same rank to ties but skips numbers. DENSE_RANK() assigns the same rank to ties without skipping.

SELECT salesperson, amount,
       RANK() OVER (ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rnk
FROM sales;

SUM() as a Running Total

SELECT salesperson, region, amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY sale_id) AS running_total
FROM sales;

This shows a cumulative running total of sales per region, row by row.

LAG() and LEAD()

LAG() accesses the previous row's value. LEAD() accesses the next row's value.

SELECT salesperson, amount,
       LAG(amount) OVER (PARTITION BY salesperson ORDER BY sale_id) AS prev_sale,
       LEAD(amount) OVER (PARTITION BY salesperson ORDER BY sale_id) AS next_sale
FROM sales;

NTILE()

Divides rows into a specified number of equal groups (buckets).

SELECT salesperson, amount,
       NTILE(3) OVER (ORDER BY amount DESC) AS bucket
FROM sales;

Finding Top N per Group Using ROW_NUMBER

SELECT salesperson, region, amount
FROM (
    SELECT salesperson, region, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
) AS ranked
WHERE rn = 1;

This returns the highest sale per region.

Key Points

• Window functions calculate across rows without collapsing the result into fewer rows.
• PARTITION BY groups rows for the calculation; ORDER BY defines their sequence.
• ROW_NUMBER() gives unique sequential numbers; RANK() and DENSE_RANK() handle ties differently.
• LAG() and LEAD() access values from previous and next rows respectively.
• Window functions are available from MySQL 8.0 onwards.