SQL MIN() and MAX()
Chapter 13 of 40 · Estimated read time: 6 min
MIN() returns the smallest value in a column. MAX() returns the largest. Both are aggregate functions — they collapse many rows into a single result. They work on numbers, dates, and even text (alphabetical order for text).
SELECT MIN(column_name) FROM table_name;
SELECT MAX(column_name) FROM table_name;
MIN() and MAX() both ignore NULL values automatically. If a column has 5 values and 2 are NULL, MIN/MAX operate on the 3 non-NULL values only.
Example 1 — Cheapest and Most Expensive Product
SELECT MIN(price) AS cheapest, MAX(price) AS most_expensive FROM products;
| cheapest | most_expensive |
|---|---|
| 49 | 8999 |
You can run MIN and MAX in the same SELECT — no need for two separate queries.
Example 2 — MIN/MAX Within a Category
-- Cheapest product in the Electronics category SELECT MIN(price) AS cheapest_electronics FROM products WHERE category = 'Electronics';
| cheapest_electronics |
|---|
| 299 |
-- Most expensive product in Furniture SELECT MAX(price) AS most_expensive_furniture FROM products WHERE category = 'Furniture';
| most_expensive_furniture |
|---|
| 8999 |
Example 3 — MIN/MAX per Category with GROUP BY
Combine MIN/MAX with GROUP BY to get the cheapest and most expensive product in every category at once:
SELECT category, MIN(price) AS cheapest, MAX(price) AS most_expensive FROM products GROUP BY category ORDER BY category;
| category | cheapest | most_expensive |
|---|---|---|
| Electronics | 299 | 1499 |
| Furniture | 1199 | 8999 |
| Stationery | 49 | 99 |
Example 4 — Earliest and Latest Date
-- Earliest and most recent order date SELECT MIN(order_date) AS first_order, MAX(order_date) AS latest_order FROM orders;
| first_order | latest_order |
|---|---|
| 2026-10-01 | 2026-10-08 |
MIN on a date column returns the earliest date; MAX returns the most recent. This is the same logic as numbers — "smallest" date = earliest in time.
Example 5 — Using MIN/MAX in a Calculation
-- Price range (spread between cheapest and most expensive) SELECT MAX(price) - MIN(price) AS price_range FROM products;
| price_range |
|---|
| 8950 |
MIN/MAX on Text Columns
-- First and last product name alphabetically SELECT MIN(product_name) AS first_alphabetically, MAX(product_name) AS last_alphabetically FROM products;
| first_alphabetically | last_alphabetically |
|---|---|
| Ballpoint Pens | Wireless Mouse |
On text columns, MIN returns the value that comes first alphabetically (A → Z); MAX returns the last (Z → A). Useful for finding the first or last name in a list.
Common Mistake — Mixing Aggregate and Non-Aggregate
-- ❌ This will fail in most databases: SELECT product_name, MIN(price) FROM products; -- You cannot mix a plain column (product_name) with an aggregate (MIN) -- unless product_name is in a GROUP BY clause. -- ✅ To get the name of the cheapest product, use a subquery: SELECT product_name, price FROM products WHERE price = (SELECT MIN(price) FROM products);
Cheapest product with its name
| product_name | price |
|---|---|
| Ballpoint Pens | 49 |
✏️ Practice Exercises
1. Find the minimum and maximum total_amount across all orders.
2. Find the most expensive product in each category.
3. Which customer placed the earliest order? (Hint: use MIN on order_date, then match with a subquery.)
Show Answers
-- 1. SELECT MIN(total_amount) AS min_order, MAX(total_amount) AS max_order FROM orders; -- 2. SELECT category, MAX(price) AS most_expensive FROM products GROUP BY category; -- 3. SELECT customer_id, order_date FROM orders WHERE order_date = (SELECT MIN(order_date) FROM orders);