↕️MIN() and MAX()

Extrema follow the column type

MIN and MAX pick one value per group according to the column domain: numeric order for numbers, chronological order for timestamps, collation order for strings. If every input in the group is NULL, the result is NULL in standard SQL, do not treat that as “epoch” or zero without an explicit COALESCE policy.

SQL

Per-group min / max

SELECT instrument_id,
       MIN(started_at) AS first_run,
       MAX(started_at) AS last_run
FROM runs
GROUP BY instrument_id;

✓Illustrative result set

instrument_id	first_run	last_run
SEQ-01	2025-11-02 08:00	2026-04-10 19:12
SEQ-02	2026-01-05 07:55	2026-04-11 06:40

MIN/MAX after a join duplicated keys

Joining runs to a slowly changing dimension without valid-time predicates can duplicate each run row,MAX(end_time) is then “max of duplicates,” not “last instrument state.”

Deduplicate or aggregate upstream

Collapse to one row per natural key in a subquery, or use a window like ROW_NUMBER() with an explicit ORDER BY when you need deterministic “latest per id.”

SQL

Pick one row per key (PostgreSQL DISTINCT ON)

SELECT DISTINCT ON (study_id) study_id, site_id, enrolled
FROM enrollments
ORDER BY study_id, enrolled DESC;

✓Illustrative result set

study_id	site_id	enrolled
ST-10	S-03	240
ST-11	S-01	180

🕒Time zones

MIN/MAX on timestamptz still compare instants; reporting “local calendar day” belongs in the SELECT layer or a documented transform.

Mixing UTC storage with ad hoc AT TIME ZONE in only some queries produces dashboards that disagree with finance, pick one convention and test boundary nights.

⚡ Advanced / dialect note

For single-group MIN/MAX on indexed columns, optimizers often answer from the index b-tree ends, verify with EXPLAIN instead of assuming a full scan.

Ties

MIN/MAX alone do not tell you which row carried that value; for “row with latest timestamp,” prefer windows or DISTINCT ON with a deterministic tie-break column.

TL;DR

MIN and MAX return the smallest and largest non-NULL values in the group (or whole table without GROUP BY). They ignore NULL inputs; if all inputs are NULL, the result is NULL. They do not identify which row held the extremum, use window functions (ROW_NUMBER() ordered by the sort key) when you need the full row.