Window Functions Aggregates MAX OVER · top-N per group · high water

MAX Window Function

MAX with OVER() — the highest value of the window, attached to every row. Combine with a CTE filter for top-N per group, or with ORDER BY for a high-water-mark running maximum.

MAX as a window function

MAX as a window function returns the largest value in the window for every row. Two everyday uses:

Group max alongside detail — every row carries its partition's highest value, so you can see how each row compares.
Running maximum — with ORDER BY, every row shows the highest value seen so far.

Syntax

MAX(expression) OVER (
    [PARTITION BY column_list]
    [ORDER BY column_list]
    [frame_clause]
)

Demo dataset

SQL — Create & insert

CREATE TABLE employees (
    employee_id    INT,
    first_name     VARCHAR(50),
    last_name      VARCHAR(50),
    salary         DECIMAL(10, 2),
    manager_id     INT,
    department_id  INT
);

INSERT INTO employees VALUES
(200, 'Jennifer',  'Whalen',     4400.00, 101, 1),
(201, 'Michael',   'Hartstein', 13000.00, 100, 2),
(202, 'Pat',       'Fay',        6000.00, 201, 2),
(203, 'Susan',     'Mavris',     6500.00, 101, 4),
(204, 'Hermann',   'Baer',      10000.00, 101, 4),
(205, 'Shelley',   'Higgins',   12008.00, 101, 1),
(206, 'William',   'Gietz',      8300.00, 205, 1),
(100, 'Steven',    'King',      24000.00, NULL, 9),
(101, 'Neena',     'Kochhar',   17000.00, 100, 9),
(102, 'Lex',       'De Haan',   17000.00, 100, 9);

Example 1 — top salary on every row

SQL

SELECT first_name,
       salary,
       MAX(salary) OVER () AS top_salary
FROM   employees;

Example 2 — department's highest salary per row

SQL

SELECT first_name,
       department_id,
       salary,
       MAX(salary) OVER (PARTITION BY department_id) AS dept_top
FROM   employees
ORDER  BY department_id, salary DESC;

Every employee row shows their own salary alongside the highest-earning person's salary in their department. To filter for "top earner per department" use this pattern wrapped in a CTE: WHERE salary = dept_top.

Example 3 — find the top earners per department

SQL

WITH tagged AS (
    SELECT first_name, department_id, salary,
           MAX(salary) OVER (PARTITION BY department_id) AS dept_top
    FROM   employees
)
SELECT first_name, department_id, salary
FROM   tagged
WHERE  salary = dept_top;

This returns each department's highest-paid employee — including ties, where two or more people share the top salary.

Example 4 — running maximum

SQL

SELECT employee_id, salary,
       MAX(salary) OVER (ORDER BY employee_id) AS running_max
FROM   employees
ORDER  BY employee_id;

The running maximum only ever increases — useful for "high water mark" reporting, like peak sales as the day progresses or peak inventory through a season.

Recap

MAX(...) OVER (PARTITION BY col) — group's highest value tagged on each row.
Combine with a CTE + filter to fetch the actual top-earner row(s) per group.
With ORDER BY: a non-decreasing running maximum.
NULLs are skipped; an all-NULL window returns NULL.