MIN() OVER
SQL · Window Functions
Theory
MIN(column) OVER (...) returns the smallest value in the window and shows it on every row. With PARTITION BY dept, each employee row carries the lowest salary of their department, so you can compute salary - dept_min or flag the lowest earner with CASE WHEN salary = dept_min THEN 'Yes' END, all in one query and without collapsing rows.
With ORDER BY inside OVER, the default frame runs from the start of the partition to the current row, so MIN becomes a running minimum: the lowest value seen so far. That is useful for tracking things like 'lowest price so far' or 'best (fastest) time so far'. NULLs are ignored. Unlike FIRST_VALUE, MIN only returns the value itself; if you also need the name of whoever holds the minimum, use FIRST_VALUE(name) with ORDER BY salary ASC.
Why we use it
Comparing each row to its group's floor is common: how much above the cheapest supplier is each quote, who is the lowest earner per team. MIN() OVER gives that floor on every row in one pass.
When to use it
Use MIN() OVER to compare rows with their group minimum or to track a running minimum. Use FIRST_VALUE when you need another column from the minimum row, and GROUP BY MIN when one row per group is enough.
How it works
- Rows split by PARTITION BY
- ORDER BY inside OVER? Yes: Running minimum so far. No: Minimum of whole partition.
- Ignore NULL values
- Write the minimum on every row
- Compare, e.g. CASE WHEN salary = dept_min
- Rows kept with their group minimum
Syntax
MIN(column) OVER (PARTITION BY col)Example
SELECT name, department, salary,
MIN(salary) OVER (PARTITION BY department) AS dept_min
FROM employees;Key points
- Lets you compare each row to its group minimum in the same query
- Often used to flag the lowest earner per department
- With ORDER BY it becomes a running minimum
- Ties: every row equal to the minimum gets flagged
- Need the holder's name? Use FIRST_VALUE(name) ORDER BY salary