MAX() OVER
SQL · Window Functions
Theory
MAX(column) OVER (...) returns the largest value in the window on every row. With PARTITION BY dept it shows each department's top salary next to each employee; with ORDER BY it becomes a running maximum (the highest value so far, such as a record high price).
A very common pattern is finding the top earner in each group. You might try WHERE salary = MAX(salary) OVER (PARTITION BY dept), but that fails: window functions are computed after WHERE, so they are not allowed there. Instead, compute the window in a subquery or CTE and filter outside: SELECT * FROM (SELECT ..., MAX(salary) OVER (PARTITION BY dept) AS dept_max FROM employees) t WHERE salary = dept_max. Unlike ROW_NUMBER = 1, this keeps all tied top earners. Snowflake, BigQuery and a few others also support QUALIFY to filter on a window result directly.
Why we use it
Showing the group's peak next to each row answers 'how far from the top am I' and makes 'top row(s) per group' queries easy. MAX() OVER does it in one scan instead of joining to a GROUP BY subquery.
When to use it
Use MAX() OVER to compare with the group maximum, to keep all tied top rows, or for a running maximum. Use ROW_NUMBER when you need exactly one row per group even if values tie.
How it works
- Inner query: compute MAX(salary) OVER (PARTITION BY dept)
- Every row now carries its department maximum
- Window used in WHERE? Yes: Error: move it to a subquery. No: Continue.
- Outer query keeps rows WHERE salary = dept_max
- Top earner(s) per department, ties included
Syntax
MAX(column) OVER (PARTITION BY col)Example
SELECT name, department, salary,
MAX(salary) OVER (PARTITION BY department) AS dept_max
FROM employees;Key points
- Lets you compare each row to its group maximum in the same query
- Not allowed in WHERE: filter on it from a subquery or CTE
- salary = dept_max keeps all tied top earners, unlike rn = 1
- With ORDER BY it becomes a running maximum
- QUALIFY (Snowflake, BigQuery) filters window results directly