SQL - MIN() and MAX() Functions
SQL aggregate functions transform multiple rows into single summary values. They're the workhorses of reporting, analytics, and data validation. While COUNT(), SUM(), and AVG() get plenty of...
Key Insights
- MIN() and MAX() are aggregate functions that collapse multiple rows into a single result, making them essential for reporting, validation, and finding boundary values in your data.
- These functions work across all comparable data types—numbers, dates, and strings—but understanding how each type is compared prevents subtle bugs in your queries.
- Combining MIN()/MAX() with GROUP BY and HAVING unlocks powerful analytical queries, while subqueries let you retrieve entire rows associated with extreme values.
Introduction to Aggregate Functions
SQL aggregate functions transform multiple rows into single summary values. They’re the workhorses of reporting, analytics, and data validation. While COUNT(), SUM(), and AVG() get plenty of attention, MIN() and MAX() are equally fundamental—and often underutilized.
These two functions answer simple but critical questions: What’s the smallest value? What’s the largest? In practice, this translates to finding your cheapest product, your highest-paid employee, your oldest order, or your most recent login. Every production database I’ve worked with uses these functions somewhere in its reporting stack.
The beauty of MIN() and MAX() lies in their simplicity. They do one thing well. But combine them with GROUP BY, HAVING, and subqueries, and you’ve got a powerful toolkit for slicing through data.
MIN() Function Basics
MIN() returns the smallest value in a column. The syntax is straightforward:
SELECT MIN(column_name)
FROM table_name
WHERE condition;
The function scans all rows matching your WHERE clause (or all rows if no WHERE exists) and returns the minimum value it finds. Here’s a practical example finding the lowest-priced product:
SELECT MIN(price) AS lowest_price
FROM products
WHERE category = 'Electronics';
This returns a single row with a single value. That’s the nature of aggregate functions—they collapse your result set.
You can also use MIN() alongside other columns and aggregates:
SELECT
category,
MIN(price) AS lowest_price,
COUNT(*) AS product_count
FROM products
GROUP BY category;
One thing to note: MIN() ignores NULL values. If a column contains NULLs, they’re silently skipped. If every value in the column is NULL, MIN() returns NULL. This behavior is usually what you want, but it’s worth knowing when debugging unexpected results.
MAX() Function Basics
MAX() is MIN()’s counterpart, returning the largest value in a column:
SELECT MAX(column_name)
FROM table_name
WHERE condition;
Finding the highest salary in an employees table is the classic example:
SELECT MAX(salary) AS highest_salary
FROM employees
WHERE department_id = 5;
Like MIN(), MAX() ignores NULLs and works with any comparable data type. Here’s a more realistic query combining MAX() with other information:
SELECT
department_id,
MAX(salary) AS top_salary,
MIN(salary) AS bottom_salary,
MAX(salary) - MIN(salary) AS salary_spread
FROM employees
GROUP BY department_id;
This gives you the salary range for each department in one query—useful for compensation analysis or identifying departments with unusual pay distributions.
Using MIN()/MAX() with Different Data Types
MIN() and MAX() work with any data type that supports comparison operations. Understanding how different types compare is crucial for writing correct queries.
Numeric Types
Numbers compare as you’d expect. MIN() finds the mathematically smallest value, MAX() the largest:
SELECT
MIN(quantity) AS min_qty,
MAX(quantity) AS max_qty,
MIN(unit_price) AS cheapest,
MAX(unit_price) AS most_expensive
FROM order_items;
Date and Time Types
Dates compare chronologically. MIN() returns the earliest date, MAX() the most recent:
SELECT
MIN(order_date) AS first_order,
MAX(order_date) AS latest_order,
DATEDIFF(day, MIN(order_date), MAX(order_date)) AS days_active
FROM orders
WHERE customer_id = 1042;
This query tells you when a customer first ordered, when they last ordered, and how many days span their order history. It’s a simple but effective customer activity summary.
String Types
Strings compare alphabetically (technically, by their collation order). MIN() returns the string that would sort first, MAX() the one that would sort last:
SELECT
MIN(last_name) AS first_alphabetically,
MAX(last_name) AS last_alphabetically
FROM employees;
This behavior catches some developers off guard. If you have product codes like ‘A100’, ‘A99’, ‘B1’, MIN() returns ‘A100’ (not ‘A99’) because string comparison is character-by-character, not numeric. The string ‘100’ comes before ‘99’ alphabetically because ‘1’ < ‘9’.
-- This might not return what you expect
SELECT MIN(product_code) FROM products;
-- 'A100' < 'A99' in string comparison
-- If you need numeric ordering, cast or extract the number
SELECT MIN(CAST(SUBSTRING(product_code, 2, 10) AS INT))
FROM products;
Combining with GROUP BY
GROUP BY transforms MIN() and MAX() from simple lookups into powerful analytical tools. Instead of finding one extreme value for the entire table, you find extreme values within each group.
SELECT
region,
MAX(sale_amount) AS highest_sale,
MIN(sale_amount) AS lowest_sale
FROM sales
WHERE sale_date >= '2024-01-01'
GROUP BY region
ORDER BY highest_sale DESC;
This returns one row per region, showing the sales range for each. Here’s a more complex example finding the oldest and newest employee in each department:
SELECT
d.department_name,
MIN(e.hire_date) AS earliest_hire,
MAX(e.hire_date) AS latest_hire,
COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY d.department_name;
The LEFT JOIN ensures departments with no employees still appear in results (with NULL dates). This is a common pattern for reporting queries where you want complete coverage of your dimension tables.
You can group by multiple columns for finer granularity:
SELECT
YEAR(order_date) AS order_year,
MONTH(order_date) AS order_month,
customer_segment,
MAX(order_total) AS largest_order,
MIN(order_total) AS smallest_order
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date), customer_segment
ORDER BY order_year, order_month, customer_segment;
Filtering with HAVING
WHERE filters rows before aggregation. HAVING filters groups after aggregation. When you need to filter based on MIN() or MAX() results, HAVING is your tool.
SELECT
department_id,
MAX(salary) AS max_salary
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 100000
ORDER BY max_salary DESC;
This returns only departments where someone earns over $100,000. You can’t do this with WHERE because the salary threshold applies to the aggregated result, not individual rows.
Here’s a more practical example finding product categories with concerning price ranges:
SELECT
category,
MIN(price) AS min_price,
MAX(price) AS max_price,
MAX(price) - MIN(price) AS price_spread
FROM products
GROUP BY category
HAVING MAX(price) > 10 * MIN(price)
ORDER BY price_spread DESC;
This identifies categories where the most expensive item costs more than 10 times the cheapest—potentially indicating pricing inconsistencies or categorization problems.
You can combine HAVING conditions:
SELECT
store_id,
MIN(transaction_date) AS first_transaction,
MAX(transaction_date) AS last_transaction
FROM transactions
GROUP BY store_id
HAVING MIN(transaction_date) < '2020-01-01'
AND MAX(transaction_date) > '2024-01-01';
This finds stores that have been active for a long time—their first transaction was before 2020 and their most recent was in 2024 or later.
Practical Use Cases and Tips
Retrieving the Full Row for Extreme Values
A common requirement is fetching the entire row associated with a MIN() or MAX() value. You can’t do this directly with aggregate functions—you need a subquery or window function.
-- Find the highest-paid employee (full row)
SELECT *
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
For grouped extremes, use a correlated subquery:
-- Highest-paid employee in each department
SELECT e.*
FROM employees e
WHERE e.salary = (
SELECT MAX(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
);
Or use window functions for better performance on large tables:
SELECT *
FROM (
SELECT
*,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees
) ranked
WHERE salary_rank = 1;
Finding Date Ranges
MIN() and MAX() on dates are perfect for calculating durations:
SELECT
project_id,
MIN(task_start_date) AS project_start,
MAX(task_end_date) AS project_end,
DATEDIFF(day, MIN(task_start_date), MAX(task_end_date)) AS project_duration_days
FROM project_tasks
GROUP BY project_id;
Handling NULLs Explicitly
If NULL handling matters for your use case, be explicit:
SELECT
COUNT(*) AS total_rows,
COUNT(price) AS rows_with_price,
MIN(price) AS min_price,
MIN(COALESCE(price, 0)) AS min_price_treating_null_as_zero
FROM products;
Performance Considerations
MIN() and MAX() on indexed columns are fast—the database can often find the value directly from the index without scanning the table. If you’re frequently querying MIN()/MAX() on a column, ensure it’s indexed.
-- This is fast if created_at is indexed
SELECT MAX(created_at) FROM orders;
-- This might be slow without an index on (customer_id, created_at)
SELECT customer_id, MAX(created_at)
FROM orders
GROUP BY customer_id;
MIN() and MAX() are deceptively simple functions. Master them, combine them with GROUP BY and subqueries, and you’ll handle a surprising range of analytical queries without reaching for more complex tools.