MIN and MAX — Smallest and Largest
"What is our cheapest product?" "When was the most recent order?" "Which customer name comes first alphabetically?" These are all the same question in disguise — find the extreme value in a column — and SQL answers them with MIN and MAX.
The rule
MIN and MAX return one extreme value — of numbers, text or dates.
Our products table:
| product_id | name | category | price |
|---|---|---|---|
| 1 | Notebook | stationery | 60.00 |
| 2 | Gel pen | stationery | 25.00 |
| 3 | Desk lamp | home | 899.00 |
| 4 | Stapler | stationery | 150.00 |
SELECT MIN(price), MAX(price) FROM products; -- 25.00, 899.00
SELECT MIN(name) FROM products; -- 'Desk lamp' (first alphabetically)
SELECT MAX(order_date) FROM orders; -- the most recent dateUnlike SUM and AVG, MIN and MAX work on anything that can be put in order: numbers by size, text alphabetically, dates by time. Like the other aggregates, they ignore NULLs, and over zero rows they return NULL.
The important limit: MIN and MAX give you the value, not the row it came from. This query looks natural but is wrong:
SELECT name, MAX(price) FROM products; -- rejected by most databasesMAX collapses four rows into one, so there is no single name to show next to it. PostgreSQL and SQL Server reject it; some settings of MySQL and SQLite accept it and pick a name for you — do not rely on that. To get the whole row, sort and take the first, or compare against the extreme:
SELECT name, price FROM products ORDER BY price DESC LIMIT 1;
SELECT name, price FROM products WHERE price = (SELECT MAX(price) FROM products);The second form also returns every product tied for the top price.
The shortcut
Ask: do I want the value, or the thing that has the value?
- The value ("what is the highest price?") →
MAX(price). - The thing ("which product is most expensive?") →
ORDER BY price DESCand take the first row, orWHERE price = (SELECT MAX(price) …).
Value or thing?
"Which item" asks for the thing, so MIN alone is not enough.
Drills
Answer fast — trust the rule.
What does SELECT MAX(price) FROM products WHERE category = 'stationery'; return?
What does SELECT MIN(name) FROM products; return?
Which query shows the name of the most expensive product?
A column's values are 7, NULL and 3. What is MIN of it?
Remember
- MIN and MAX return one extreme value — of numbers, text or dates.
- Value or thing? Value → MIN/MAX; thing → sort and take one, or compare with a subquery.
- A bare column next to MAX is not "the row that has the max".