MIN and MAX as SQL Window Functions

A product manager asks why some items in the same category are priced so far above the category floor. You need to see each product's price alongside the lowest and highest price in its category, on the same row, without losing any products from the result. MIN and MAX as window functions are exactly what that query needs.

How MIN and MAX Work as Window Functions

Used with GROUP BY, MIN and MAX collapse rows. Used with OVER, they keep every row and attach the result as a new column. The function itself doesn't change. Only the context does.

The basic syntax:

SELECT
    column_name,
    MIN(column_name) OVER (PARTITION BY group_column) AS group_min,
    MAX(column_name) OVER (PARTITION BY group_column) AS group_max
FROM table_name;

PARTITION BY tells SQL which group to calculate within. Without it, MIN and MAX look at the entire result set. With it, they reset for each group.

Here's a direct example on the products table. This returns each product's price alongside the cheapest and most expensive price in the same category:

SELECT
    p.product_name,
    p.category_id,
    p.price,
    MIN(p.price) OVER (PARTITION BY p.category_id) AS category_min_price,
    MAX(p.price) OVER (PARTITION BY p.category_id) AS category_max_price
FROM products p;

Every row keeps its own price. SQL adds category_min_price and category_max_price next to it. No join. No subquery. No rows disappear.

Using MIN and MAX Without PARTITION BY

Drop PARTITION BY and the calculation covers the entire table. Every row gets the same global minimum or maximum.

SELECT
    p.product_name,
    p.price,
    MIN(p.price) OVER () AS lowest_price,
    MAX(p.price) OVER () AS highest_price
FROM products p;

This is useful when you want to see how far each row sits from the overall floor or ceiling. The empty OVER () is valid SQL. It just means: no partitioning, no ordering, calculate across all rows.

Running Minimum and Maximum with ORDER BY

Add ORDER BY inside OVER and the behaviour changes. SQL processes rows one at a time in the order you specify. MIN tracks the lowest value seen so far. MAX tracks the highest.

This query shows a running maximum of total_amount across orders, sorted by date:

SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    MAX(o.total_amount) OVER (ORDER BY o.order_date) AS running_max_order
FROM orders o;

As SQL moves forward through order_date, running_max_order updates whenever a new high appears. Earlier rows keep the maximum that was true at that point in time.

You can combine PARTITION BY and ORDER BY to get a running maximum that resets within each group:

SELECT
    o.order_id,
    o.status,
    o.order_date,
    o.total_amount,
    MAX(o.total_amount) OVER (PARTITION BY o.status ORDER BY o.order_date) AS running_max_by_status
FROM orders o;

Each status group tracks its own running high independently.

Comparing Each Row to Its Group Range

One of the most useful things you can do with MIN and MAX as window functions is compute the gap between a row's value and the group boundary. You can do that directly in the SELECT list.

This query shows how far each employee's salary sits above the lowest salary in their department:

SELECT
    e.employee_id,
    e.department,
    e.salary,
    MIN(e.salary) OVER (PARTITION BY e.department) AS dept_min,
    e.salary - MIN(e.salary) OVER (PARTITION BY e.department) AS above_dept_min
FROM employees e;

No joins, no subqueries. The arithmetic happens in the same SELECT. This pattern works equally well with MAX if you want to show how far each row sits below the group ceiling.

How This Connects to Other Concepts

MIN and MAX as window functions use the same OVER clause as [SUM, AVG, and COUNT window functions](/learn/sql-sum-avg-count-window). If you're already comfortable with those, the syntax here is identical. Only the aggregate changes.

If you need to filter rows based on the window result, for example, returning only products priced at their category minimum, you'll need to wrap the query in a [subquery or CTE](/learn/sql-cte-common-table-expressions). You can't reference a window function alias in a WHERE clause at the same query level.

Common Mistakes

1. Trying to filter on a window alias in WHERE

SQL evaluates WHERE before window functions run, so the alias doesn't exist yet at that point.

-- wrong
SELECT
    p.product_name,
    p.price,
    MIN(p.price) OVER (PARTITION BY p.category_id) AS cat_min
FROM products p
WHERE p.price = cat_min;

Wrap the query so the computed column is available to filter on.

-- correct
SELECT * FROM (
    SELECT
        p.product_name,
        p.price,
        MIN(p.price) OVER (PARTITION BY p.category_id) AS cat_min
    FROM products p
) t
WHERE price = cat_min;

2. Expecting GROUP BY behavior from a window function

MIN() OVER (PARTITION BY ...) does not collapse rows. If you want one row per group showing only the minimum, use GROUP BY instead.

-- wrong expectation: one row per department with the lowest salary
SELECT
    e.department,
    MIN(e.salary) OVER (PARTITION BY e.department) AS dept_min
FROM employees e;

Use GROUP BY when you want a summary, not a window function.

-- correct: one row per department
SELECT
    e.department,
    MIN(e.salary) AS dept_min
FROM employees e
GROUP BY e.department;

3. Confusing running MAX with global MAX

Adding ORDER BY inside OVER changes MAX from a static group maximum into a cumulative one. The value on each row reflects only the rows processed so far, not the full group.

-- this is a running max, not a static group max
SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    MAX(o.total_amount) OVER (ORDER BY o.order_date) AS running_max
FROM orders o;

Remove ORDER BY from inside OVER if you want the same global maximum on every row.

-- correct for a static global max
SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    MAX(o.total_amount) OVER () AS global_max
FROM orders o;

Quick Recap

• MIN() and MAX() with OVER add the group minimum or maximum to each row without collapsing the result set.
• PARTITION BY inside OVER resets the calculation for each group, similar to how GROUP BY defines groups in aggregate queries.
• Omitting PARTITION BY applies MIN or MAX across the entire result set, giving every row the same global value.
• Adding ORDER BY inside OVER produces a running minimum or maximum that updates row by row as SQL moves through the sorted result.
• You can subtract the window result from the row's own value directly in SELECT to compute the gap from the group boundary.
• To filter on a window function result, wrap the query in a subquery or [CTE](/learn/sql-cte-common-table-expressions) and apply WHERE in the outer query.

SELECT
    e.employee_id,
    e.department,
    e.salary,
    MIN(e.salary) OVER (PARTITION BY e.department) AS dept_min_salary
FROM employees e;

SELECT
    e.employee_id,
    e.department,
    e.salary,
    MAX(e.salary) OVER (PARTITION BY e.department) AS dept_max_salary
FROM employees e;

SELECT
    pay.payment_id,
    pay.method,
    pay.amount,
    MIN(pay.amount) OVER (PARTITION BY pay.method) AS method_min,
    MAX(pay.amount) OVER (PARTITION BY pay.method) AS method_max
FROM payments pay;

SELECT
    pay.payment_id,
    pay.method,
    pay.amount,
    pay.amount - MIN(pay.amount) OVER (PARTITION BY pay.method) AS above_method_min
FROM payments pay
WHERE pay.status = 'completed';

SELECT * FROM (
    SELECT
        e.employee_id,
        e.department,
        e.salary,
        MIN(e.salary) OVER (PARTITION BY e.department) AS dept_min_salary
    FROM employees e
) t
WHERE salary = dept_min_salary;