MIN / MAX
The smallest or largest value in a group — for numbers, dates, and text — and how to get the whole row that owns it.
Syntax
MIN(expression)
MAX(expression)| Parameter | Type | Notes |
|---|---|---|
| expression* | any orderable type | Numbers, dates, or text. |
What it does
SQL playground
Loading editor…
⌘/Ctrl + Enter
MIN/MAX don't tell you the rest of the row
MAX(amount) above is the largest amount per customer — it doesn't say
which order_id that was, and other columns in the same SELECT aren't
guaranteed to line up with it. To get the full row that owns the extreme
value, rank and filter instead:
SQL playground
Loading editor…
⌘/Ctrl + Enter
This is the top N per group pattern with N = 1.
Text sorts lexicographically
MAX('9') is greater than MAX('10') as text — '9' > '10' character by
character. If a column holding numeric-looking text is compared this way,
cast it: MAX(CAST(code AS INTEGER)).
Gotchas
- NULLs are ignored; a group of all-
NULLvalues returnsNULL. - Text vs numeric sort order — confirm the column type sorts the way the query assumes.
- Don't assume other
SELECTed columns match the MAX row — see above.
- Top N per group — the general form of "the row with the max value."
- ORDER BY — the same NULL-ordering and type-sorting rules apply here.
- Type coercion — why the text-vs-numeric sort surprise happens in the first place.
Safety checklist
- confirm the column type sorts the way you expect (numeric vs text vs date)
- decide whether an all-NULL group should yield NULL or a fallback
- rank the rows if you need the whole row, don't assume other columns line up
Was this page helpful?