SQL Tutorial › Window Functions › Window Functions

SQL Window Functions

Q: What is the difference between PARTITION BY and GROUP BY?

GROUP BY returns one row per group. PARTITION BY defines groups for a window function while keeping every row in the result.

Q: Does MySQL support window functions?

Yes, from MySQL 8.0, including ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE and aggregates with OVER.

Q: Why can't I filter on a window function in WHERE?

Because window functions are evaluated after WHERE. Compute them in a CTE or subquery and filter in the outer query.

AdvancedLesson 45 of 6914 min read9 queries run on MySQL 8.4

A window function calculates a value across a set of related rows (the window) without collapsing them into one row the way GROUP BY does. You keep every employee row and still see the department average, the rank inside the department or the running total. Window functions are the most asked advanced topic in SQL interviews, and MySQL supports them from version 8.0.

In this lesson

How OVER() differs from GROUP BY
Split rows into windows with PARTITION BY
Order a window with ORDER BY for running results
Reuse a window with WINDOW and control the frame

GROUP BY vs window function

GROUP BY returns one row per department:

SQL

SELECT dept_id, ROUND(AVG(salary)) AS dept_avgFROM employeesWHERE dept_id IN (1, 2)GROUP BY dept_id;

ResultMySQL 8.4

dept_id	dept_avg
1	78400
2	69000

2 rows in set

A window function keeps every row and adds the department average to each one:

SQL

SELECT first_name, dept_id, salary,       ROUND(AVG(salary) OVER (PARTITION BY dept_id)) AS dept_avgFROM employeesWHERE dept_id IN (1, 2)ORDER BY dept_id, salary DESC;

ResultMySQL 8.4

first_name	dept_id	salary	dept_avg
Asha	1	125000	78400
Rahul	1	98000	78400
Priya	1	72000	78400
Karan	1	72000	78400
Amit	1	25000	78400
Vikram	2	105000	69000
Neha	2	54000	69000
Arjun	2	48000	69000

8 rows in set

How this query works, step by step

OVER (...) turns AVG into a window function.
PARTITION BY dept_id splits the rows into windows, one per department.
For each row, AVG is computed over that row’s window: Engineering rows all get 78,400 and Sales rows 69,000.
No row disappears: 8 rows in, 8 rows out.

Window function syntax

MySQL

function_name(args) OVER (  [PARTITION BY columns]  [ORDER BY columns]  [frame clause])

Part	Does
`PARTITION BY`	divides rows into independent groups; without it the whole result is one window
`ORDER BY`	orders rows inside each window; needed for ranking, `LAG`/`LEAD` and running totals
frame	which rows around the current one to include, e.g. `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`

Sample data

employees · 12 rows (some columns)

emp_id	first_name	dept_id	salary	hire_date
100	Meera	NULL	250000	2015-01-05
101	Asha	1	125000	2018-04-12
102	Rahul	1	98000	2019-07-01
103	Priya	1	72000	2021-01-18
104	Karan	1	72000	2022-08-29
105	Vikram	2	105000	2017-11-20
106	Neha	2	54000	2022-03-14
107	Arjun	2	48000	2023-06-05
108	Kavya	3	88000	2020-09-09
109	Rohan	3	45000	2024-01-22
110	Sneha	4	78000	2019-02-25
111	Amit	1	25000	2025-06-02

OVER() with no partition

An empty OVER () makes the whole result one window. Each salary as a share of the total payroll:

MySQL

SELECT first_name, salary,       SUM(salary) OVER () AS total_payroll,       ROUND(100 * salary / SUM(salary) OVER (), 1) AS pct_of_payrollFROM employeesORDER BY salary DESCLIMIT 5;

ResultMySQL 8.4

first_name	salary	total_payroll	pct_of_payroll
Meera	250000	1060000	23.6
Asha	125000	1060000	11.8
Vikram	105000	1060000	9.9
Rahul	98000	1060000	9.2
Kavya	88000	1060000	8.3

5 rows in set

How this query works, step by step

SUM(salary) OVER () is the sum of all 12 salaries, repeated on every row: 1,060,000.
Dividing each salary by it gives its share.
Window functions are computed before ORDER BY and LIMIT, so the total still covers all 12 rows even though only 5 are shown.

Ranking functions

SQL

SELECT first_name, dept_id, salary,       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS row_num,       RANK()       OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk,       DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rnkFROM employeesWHERE dept_id = 1;

ResultMySQL 8.4

first_name	dept_id	salary	row_num	rnk	dense_rnk
Asha	1	125000	1	1	1
Rahul	1	98000	2	2	2
Priya	1	72000	3	3	3
Karan	1	72000	4	3	3
Amit	1	25000	5	5	4

5 rows in set

How this query works, step by step

ORDER BY salary DESC inside OVER ranks from the highest salary.
Priya and Karan tie at 72,000: ROW_NUMBER still gives them 3 and 4, RANK gives both 3 and skips 4, DENSE_RANK gives both 3 without a gap.
Full lesson: ROW_NUMBER, RANK, DENSE_RANK.

ORDER BY in the window: running totals

Adding ORDER BY to an aggregate’s window makes it cumulative:

SQL

SELECT first_name, hire_date, salary,       SUM(salary) OVER (ORDER BY hire_date) AS running_payroll,       COUNT(*)    OVER (ORDER BY hire_date) AS headcountFROM employeesORDER BY hire_date;

ResultMySQL 8.4

first_name	hire_date	salary	running_payroll	headcount
Meera	2015-01-05	250000	250000	1
Vikram	2017-11-20	105000	355000	2
Asha	2018-04-12	125000	480000	3
Sneha	2019-02-25	78000	558000	4
Rahul	2019-07-01	98000	656000	5
Kavya	2020-09-09	88000	744000	6
Priya	2021-01-18	72000	816000	7
Neha	2022-03-14	54000	870000	8
Karan	2022-08-29	72000	942000	9
Arjun	2023-06-05	48000	990000	10
Rohan	2024-01-22	45000	1035000	11
Amit	2025-06-02	25000	1060000	12

12 rows in set

How this query works, step by step

Rows are ordered by hire_date inside the window.
For each row, SUM adds the salaries of everyone hired up to and including that date.
COUNT(*) the same way gives the headcount over time. More in Running Totals.

Values from other rows: LAG and LEAD

SQL

SELECT first_name, hire_date,       LAG(first_name)  OVER (ORDER BY hire_date) AS hired_before,       LEAD(hire_date)  OVER (ORDER BY hire_date) AS next_hire_dateFROM employeesORDER BY hire_dateLIMIT 6;

ResultMySQL 8.4

first_name	hire_date	hired_before	next_hire_date
Meera	2015-01-05	NULL	2017-11-20
Vikram	2017-11-20	Meera	2018-04-12
Asha	2018-04-12	Vikram	2019-02-25
Sneha	2019-02-25	Asha	2019-07-01
Rahul	2019-07-01	Sneha	2020-09-09
Kavya	2020-09-09	Rahul	2021-01-18

6 rows in set

Full lesson: LAG & LEAD.

Named windows

When several functions use the same window, define it once with WINDOW:

SQL

SELECT first_name, dept_id, salary,       MIN(salary) OVER w AS dept_min,       MAX(salary) OVER w AS dept_max,       salary - MIN(salary) OVER w AS above_minFROM employeesWHERE dept_id IS NOT NULLWINDOW w AS (PARTITION BY dept_id)ORDER BY dept_id, salary;

ResultMySQL 8.4

first_name	dept_id	salary	dept_min	dept_max	above_min
Amit	1	25000	25000	125000	0
Priya	1	72000	25000	125000	47000
Karan	1	72000	25000	125000	47000
Rahul	1	98000	25000	125000	73000
Asha	1	125000	25000	125000	100000
Arjun	2	48000	48000	105000	0
Neha	2	54000	48000	105000	6000
Vikram	2	105000	48000	105000	57000
Rohan	3	45000	45000	88000	0
Kavya	3	88000	45000	88000	43000
Sneha	4	78000	78000	78000	0

11 rows in set

Where window functions are allowed

Window functions run after WHERE, GROUP BY and HAVING, so they can only appear in SELECT and ORDER BY. To filter on one, wrap the query in a CTE or subquery:

MySQL

SELECT first_name, RANK() OVER (ORDER BY salary DESC) AS rnkFROM employeesWHERE rnk <= 3;

ResultMySQL 8.4

ERROR 1054 (42S22): Unknown column 'rnk' in 'where clause'

SQL

WITH ranked AS (  SELECT first_name, salary, RANK() OVER (ORDER BY salary DESC) AS rnk  FROM employees)SELECT * FROM ranked WHERE rnk <= 3;

ResultMySQL 8.4

first_name	salary	rnk
Meera	250000	1
Asha	125000	2
Vikram	105000	3

3 rows in set

Window functions in MySQL

Group	Functions
Ranking	`ROW_NUMBER`, `RANK`, `DENSE_RANK`, `PERCENT_RANK`, `CUME_DIST`, `NTILE`
Value	`LAG`, `LEAD`, `FIRST_VALUE`, `LAST_VALUE`, `NTH_VALUE`
Aggregate	`SUM`, `AVG`, `COUNT`, `MIN`, `MAX` (and others) with `OVER`

Exercise: Salary vs department maximum

Show each employee’s first_name, dept_id, salary and the gap to the highest salary in their department, for employees with a department.

Expected resultMySQL 8.4

first_name	dept_id	salary	gap_to_top
Asha	1	125000	0
Rahul	1	98000	27000
Priya	1	72000	53000
Karan	1	72000	53000
Amit	1	25000	100000
Vikram	2	105000	0
Neha	2	54000	51000
Arjun	2	48000	57000
Kavya	3	88000	0
Rohan	3	45000	43000
Sneha	4	78000	0

11 rows in set

Hint 1

MAX(salary) OVER (PARTITION BY dept_id) - salary.

SQL

SELECT first_name, dept_id, salary,       MAX(salary) OVER (PARTITION BY dept_id) - salary AS gap_to_topFROM employeesWHERE dept_id IS NOT NULLORDER BY dept_id, salary DESC;

ResultMySQL 8.4

first_name	dept_id	salary	gap_to_top
Asha	1	125000	0
Rahul	1	98000	27000
Priya	1	72000	53000
Karan	1	72000	53000
Amit	1	25000	100000
Vikram	2	105000	0
Neha	2	54000	51000
Arjun	2	48000	57000
Kavya	3	88000	0
Rohan	3	45000	43000
Sneha	4	78000	0

11 rows in set

Quick quiz

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

2. What does PARTITION BY dept_id do?

3. Can you use a window function in WHERE?

4. Which MySQL version introduced window functions?

Window functions FAQ

What are window functions in SQL?

Functions that compute a value over a set of rows related to the current row, defined with OVER(), without collapsing the rows into groups.

What is the difference between PARTITION BY and GROUP BY?

GROUP BY returns one row per group. PARTITION BY defines groups for a window function while keeping every row in the result.

Does MySQL support window functions?

Yes, from MySQL 8.0, including ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE and aggregates with OVER.

Why can’t I filter on a window function in WHERE?

Because window functions are evaluated after WHERE. Compute them in a CTE or subquery and filter in the outer query.