Introduction
In data analysis and business intelligence, identifying the extreme boundaries of a dataset is a daily necessity. MIN and MAX answer two fundamental questions:
- MIN: What is the smallest, lowest, or earliest value? (e.g. lowest product price, earliest order date, minimum test score).
- MAX: What is the largest, highest, or latest value? (e.g. highest employee salary, latest customer sign-up, peak server load).
What Are MIN and MAX?
MIN() and MAX() are aggregate functions built into all mainstream SQL engines. They scan a set of values and return the single extreme scalar result:
SELECT MAX(price) FROM products;
Basic Syntax
MAX(expression)
The expression is usually a column name (such as price or created_at), but it can also be an arithmetic calculation.
Your First MIN Query
Given product prices: [100, 250, 80, 400]:
FROM products;
Result: 80.
Your First MAX Query
Using the same dataset [100, 250, 80, 400]:
FROM products;
Result: 400.
MIN / MAX With WHERE
When paired with a WHERE clause, the database filters the candidate rows first before locating the extreme value:
FROM employees
WHERE department = 'Sales';
SELECT MIN(price) AS cheapest_electronics
FROM products
WHERE category = 'Electronics';
Table ➔ WHERE filters rows ➔ Matching values ➔ MIN / MAX ➔ Single Result.MIN / MAX and NULL Rules
Like all standard aggregate functions, MIN and MAX completely ignore NULL values.
Given prices [100, NULL, 250, 80, NULL]:
- NULLs are discarded: only
[100, 250, 80]are evaluated. MIN()is 80 (NULL is NOT treated as 0).MAX()is 250.
MIN() and MAX() return NULL.Ignore NULL ➔ [100, 250, 80]
MIN / MAX With Duplicate Values
Duplicate values have zero impact on the final minimum or maximum.
For example, with values [100, 100, 250, 80, 80]:
MIN()is still 80.MAX()is still 250.
MIN / MAX With Dates & Timestamps
MIN and MAX work seamlessly on chronological date and timestamp columns:
MAX(order_date) AS latest_order_date
FROM orders;
MIN(order_date): Returns the earliest / oldest date in the records.MAX(order_date): Returns the latest / most recent date in the records.
MIN / MAX With Text Columns
SQL systems (such as PostgreSQL and MySQL) also support MIN and MAX on text strings using alphabetical collation ordering:
MIN(name) returns the name that comes first alphabetically (e.g. 'Aarav'), while MAX(name)returns the name that comes last (e.g. 'Zara').
MIN / MAX vs ORDER BY + LIMIT
Beginners often wonder how MIN(price) differs from sorting and taking the top row:
FROM products;
Returns a single scalar aggregate value. If table is empty, returns 1 row with NULL.
FROM products
ORDER BY price ASC
LIMIT 1;
Sorts candidate rows and limits the output rows. If table is empty, returns 0 rows.
| # | Product | Category | Price | Extreme Status |
|---|---|---|---|---|
| 1 | Wireless Mouse | Electronics | ₹250 | Valid non-NULL value |
| 2 | Cotton T-Shirt | Clothing | ₹100 | Valid non-NULL value |
| 3 | Mechanical Keyboard | Electronics | ₹800 | Valid non-NULL value |
| 4 | Clearance USB Cable | Electronics | ₹80 | ⭐ Minimum Value |
| 5 | Denim Jeans | Clothing | ₹250 | Valid non-NULL value |
| 6 | 4K Ultra Gaming Monitor | Electronics | ₹2,500 | Valid non-NULL value |
| 7 | Custom Prototype Hub | Electronics | NULL | NULL (Ignored) |
| 8 | Basic Crew Socks | Clothing | ₹80 | ⭐ Minimum Value |
| 9 | Specialty Winter Jacket | Clothing | NULL | NULL (Ignored) |
SELECT MIN(price) AS lowest_price FROM products;
lowest_priceCommon MIN / MAX Mistakes
SELECT MAX(salary) FROM employees; returns only the scalar number (e.g. ₹90,000), not the employee name who earns it.
MIN() does NOT treat NULL as zero. With values [100, NULL, 50], MIN returns 50, not 0.
COUNT counts the rows; MIN and MAX find the lowest and highest values in those rows.
MIN vs MAX Scenario Challenge
Test your intuition: choose whether you would use MIN or MAX for each real-world analytics scenario:
Practical Step-by-Step Exercises
| Task Goal | Target Table | Required SQL Solution | Function Pattern |
|---|---|---|---|
| 1. Lowest Product Price | products | SELECT MIN(price) FROM products; | Table-level MIN |
| 2. Highest Product Price | products | SELECT MAX(price) FROM products; | Table-level MAX |
| 3. Earliest Order Date | orders | SELECT MIN(order_date) FROM orders; | Date MIN |
| 4. Latest Order Date | orders | SELECT MAX(order_date) FROM orders; | Date MAX |
| 5. Top Sales Salary | employees | SELECT MAX(salary) FROM employees WHERE department = 'Sales'; | WHERE + MAX |
MIN / MAX Best Practices
- Always Alias Your Extremes: Write
SELECT MIN(price) AS lowest_pricefor intuitive reporting headers. - Filter Before Aggregating: Narrow your dataset using
WHEREclauses for specific categories or date spans. - Remember That NULL is Excluded: Never assume NULL is treated as 0 or the lowest value.
- Do Not Expect Row IDs: If you need the full row with the minimum or maximum value, use subqueries or ordering techniques.
What You Should Know Now
- ✓MIN(col): Finds smallest number, earliest date, or first text
- ✓MAX(col): Finds largest number, latest date, or last text
- ✓NULL Handling: NULL values are ignored during evaluation
- ✓Empty Sets: Returns NULL when 0 valid rows match
- ✓Duplicates: Duplicates do not alter the minimum or maximum
- ✓Scalar Output: Returns a single boundary value, not the whole row
Knowledge Assessment: MIN() & MAX() Functions
Test your understanding of lowest/highest numeric values, alphabetical string min/max, and date boundary evaluations.
What is the primary role of the SQL MIN() aggregate function?