MySQL Window Functions: OVER and PARTITION BY Explained

A window function calculates a value for each row using a set of related rows (its “window”), without collapsing the rows into one, as GROUP BY does. They arrived in MySQL 8.0.

query.sql

SQL

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    manager_id INT NULL,
    hired_on DATE NOT NULL
);

INSERT INTO employees (name, department, salary, manager_id, hired_on) VALUES
    ('Asha Rao',     'management',  150000.00, NULL, '2020-01-15'),
    ('Ben Carter',   'engineering', 120000.00,    1, '2021-03-01'),
    ('Chitra Nair',  'engineering',  95000.00,    2, '2022-07-11'),
    ('Dev Malhotra', 'engineering',  88000.00,    2, '2023-01-09'),
    ('Esha Gupta',   'sales',        70000.00,    1, '2021-09-20'),
    ('Farid Khan',   'sales',        64000.00,    5, '2022-11-02'),
    ('Gita Menon',   'sales',        64000.00,    5, '2024-02-14'),
    ('Hari Iyer',    'support',      52000.00,    1, '2023-05-30');

GROUP BY collapses. A window keeps the rows.

query.sql

SQL

SELECT department, ROUND(AVG(salary), 0) AS avg_salary FROM employees GROUP BY department ORDER BY department;

Output

OUTPUT

+-------------+------------+
| department  | avg_salary |
+-------------+------------+
| engineering |     101000 |
| management  |     150000 |
| sales       |      66000 |
| support     |      52000 |
+-------------+------------+
4 rows in set

One row per department. The employees are gone. A window function shows the department average next to each employee:

query.sql

SQL

SELECT name, department, salary,
       ROUND(AVG(salary) OVER (PARTITION BY department), 0) AS department_average
FROM employees
ORDER BY department, name;

Output

OUTPUT

+--------------+-------------+-----------+--------------------+
| name         | department  | salary    | department_average |
+--------------+-------------+-----------+--------------------+
| Ben Carter   | engineering | 120000.00 |             101000 |
| Chitra Nair  | engineering |  95000.00 |             101000 |
| Dev Malhotra | engineering |  88000.00 |             101000 |
| Asha Rao     | management  | 150000.00 |             150000 |
| Esha Gupta   | sales       |  70000.00 |              66000 |
| Farid Khan   | sales       |  64000.00 |              66000 |
| Gita Menon   | sales       |  64000.00 |              66000 |
| Hari Iyer    | support     |  52000.00 |              52000 |
+--------------+-------------+-----------+--------------------+
8 rows in set

Every employee is still in the result, with the average of their department added as a new column.

The syntax

Text

PLAIN

function_name(arguments) OVER (
    [PARTITION BY column, ...]
    [ORDER BY column [ASC|DESC], ...]
    [frame clause]
)

Part	Meaning
`OVER (...)`	Marks it as a window function
`PARTITION BY`	Splits the rows into groups (like `GROUP BY`, but without collapsing). Without it, all rows form one group
`ORDER BY`	The order inside each partition. Needed for ranking, running totals and `LAG`/`LEAD`
Frame clause	Which rows around the current row the function looks at (Frames)

Which functions can be windows

Ranking: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(n), PERCENT_RANK(), CUME_DIST().
Value access: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTH_VALUE().
Aggregates used as windows: SUM(), AVG(), COUNT(), MIN(), MAX(), and more.

Numbering rows

query.sql

SQL

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

Output

OUTPUT

+--------------+-------------+-----------+--------------------+
| name         | department  | salary    | rank_in_department |
+--------------+-------------+-----------+--------------------+
| Ben Carter   | engineering | 120000.00 |                  1 |
| Chitra Nair  | engineering |  95000.00 |                  2 |
| Dev Malhotra | engineering |  88000.00 |                  3 |
| Asha Rao     | management  | 150000.00 |                  1 |
| Esha Gupta   | sales       |  70000.00 |                  1 |
| Farid Khan   | sales       |  64000.00 |                  2 |
| Gita Menon   | sales       |  64000.00 |                  3 |
| Hari Iyer    | support     |  52000.00 |                  1 |
+--------------+-------------+-----------+--------------------+
8 rows in set

In each department, the highest earner gets 1, the next 2, and so on. The numbering restarts in each partition.

A running total

query.sql

SQL

1
2
3

SELECT name, hired_on, salary, SUM(salary) OVER (ORDER BY hired_on, id) AS payroll_so_far
FROM employees
ORDER BY hired_on, id;

Output

OUTPUT

+--------------+------------+-----------+----------------+
| name         | hired_on   | salary    | payroll_so_far |
+--------------+------------+-----------+----------------+
| Asha Rao     | 2020-01-15 | 150000.00 |      150000.00 |
| Ben Carter   | 2021-03-01 | 120000.00 |      270000.00 |
| Esha Gupta   | 2021-09-20 |  70000.00 |      340000.00 |
| Chitra Nair  | 2022-07-11 |  95000.00 |      435000.00 |
| Farid Khan   | 2022-11-02 |  64000.00 |      499000.00 |
| Dev Malhotra | 2023-01-09 |  88000.00 |      587000.00 |
| Hari Iyer    | 2023-05-30 |  52000.00 |      639000.00 |
| Gita Menon   | 2024-02-14 |  64000.00 |      703000.00 |
+--------------+------------+-----------+----------------+
8 rows in set

With an ORDER BY inside OVER, the aggregate becomes a running (cumulative) total: the sum of all rows up to the current one.

Where window functions are allowed

They are calculated after WHERE, GROUP BY and HAVING, and before DISTINCT, ORDER BY and LIMIT. So:

You can use them in the select list and in ORDER BY.
You cannot use them in WHERE, GROUP BY or HAVING:

query.sql

SQL

SELECT name FROM employees WHERE ROW_NUMBER() OVER (ORDER BY salary) = 1;

Output

OUTPUT

ERROR 3593 (HY000): You cannot use the window function 'row_number' in this context.'

To filter on a window function, wrap the query in a derived table or a CTE and filter in the outer query:

query.sql

SQL

WITH ranked AS (
    SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT name, department, salary FROM ranked WHERE rn = 1 ORDER BY department;

Output

OUTPUT

+------------+-------------+-----------+
| name       | department  | salary    |
+------------+-------------+-----------+
| Ben Carter | engineering | 120000.00 |
| Asha Rao   | management  | 150000.00 |
| Esha Gupta | sales       |  70000.00 |
| Hari Iyer  | support     |  52000.00 |
+------------+-------------+-----------+
4 rows in set

The highest-paid person of each department.

Windows and GROUP BY together

A window function can run on grouped results:

query.sql

SQL

SELECT department, SUM(salary) AS payroll,
       ROUND(100 * SUM(salary) / SUM(SUM(salary)) OVER (), 1) AS percent_of_company
FROM employees
GROUP BY department
ORDER BY payroll DESC;

Output

OUTPUT

+-------------+-----------+--------------------+
| department  | payroll   | percent_of_company |
+-------------+-----------+--------------------+
| engineering | 303000.00 |               43.1 |
| sales       | 198000.00 |               28.2 |
| management  | 150000.00 |               21.3 |
| support     |  52000.00 |                7.4 |
+-------------+-----------+--------------------+
4 rows in set

SUM(salary) is the group total. SUM(SUM(salary)) OVER () adds up those group totals over all groups, to give the company total. First the grouping happens, then the window runs over the grouped rows.

Reserved words

RANK, ROW_NUMBER, LAG, LEAD, FIRST_VALUE and others became reserved words in MySQL 8.0. If you have columns or aliases with these names, quote them with backticks, or better, rename them (Reserved Words).

Why learn them

Window functions solve, in a few clear lines, problems that needed self joins, correlated subqueries and user variables in older MySQL: running totals, rankings, top N per group, comparing a row with the previous one, moving averages, percentiles. The next lessons go through each of them.

info

Version note

MySQL 5.7 and older do not have window functions. MariaDB has them since 10.2 with small differences. If a query using OVER fails with a syntax error, check SELECT VERSION();.