Window Functions Aggregates MIN OVER · spread · low-water

MIN Window Function

The mirror of MAX as a window function — each row picks up the smallest value of its window. Combine with MAX for spreads, with ORDER BY for low-water marks, with a CTE filter for the bottom row(s) per group.

MIN as a window function

MIN mirrors MAX: every row gets the smallest value seen in its window. Same shape, opposite direction. Useful for "low-water marks", "cheapest in the category", or "earliest event so far".

Syntax

MIN(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 — minimum salary on every row

SQL

SELECT first_name,
       salary,
       MIN(salary) OVER () AS lowest_salary
FROM   employees;

Example 2 — department minimum + spread

SQL

SELECT first_name,
       department_id,
       salary,
       MIN(salary) OVER (PARTITION BY department_id) AS dept_low,
       MAX(salary) OVER (PARTITION BY department_id) AS dept_high,
       MAX(salary) OVER (PARTITION BY department_id)
         - MIN(salary) OVER (PARTITION BY department_id) AS dept_spread
FROM   employees
ORDER  BY department_id;

Combining MIN and MAX windows on the same partition gives you the spread on every row — handy for spotting departments where pay is unusually wide or narrow.

Example 3 — running minimum

SQL

SELECT employee_id, salary,
       MIN(salary) OVER (ORDER BY employee_id) AS running_min
FROM   employees
ORDER  BY employee_id;

The running minimum only ever decreases (or stays the same) as more rows are seen.

Example 4 — find each department's lowest earner

SQL

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

Recap

MIN(...) OVER (PARTITION BY col) — group's lowest value on each row.
Pair with MAX to compute spreads inline.
With ORDER BY: a non-increasing running minimum.
NULLs are skipped; an all-NULL window returns NULL.