How to Use MIN and MAX in MySQL

Key Insights

• MIN() and MAX() are aggregate functions that find the smallest and largest values in a column, working seamlessly with numeric, date, and string data types while automatically ignoring NULL values.
• Combining MIN/MAX with GROUP BY enables per-category analysis, but retrieving full row details for min/max values requires subqueries or self-joins rather than simple aggregation.
• For indexed columns, MIN/MAX queries are highly optimized, but using ORDER BY with LIMIT 1 often outperforms aggregate functions when you need the entire row, not just the min/max value.

Introduction to MIN and MAX Functions

MySQL’s MIN() and MAX() aggregate functions are workhorses for data analysis. MIN() returns the smallest value in a column, while MAX() returns the largest. These functions operate across multiple rows to produce a single result, making them essential for queries involving ranges, boundaries, and extremes.

Common use cases include finding price ranges in e-commerce catalogs, identifying the earliest and latest transaction dates, determining peak performance metrics, or discovering alphabetical boundaries in text data. Unlike simple sorting with LIMIT, MIN() and MAX() can work with grouping to provide aggregate insights across categories.

Here’s the basic syntax:

SELECT MIN(column_name), MAX(column_name)
FROM table_name;

Let’s create a sample dataset to explore these functions:

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2),
    stock_quantity INT,
    last_updated DATE
);

INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 1299.99, 15, '2024-01-15'),
(2, 'Mouse', 'Electronics', 24.99, 150, '2024-02-01'),
(3, 'Desk Chair', 'Furniture', 299.99, 30, '2024-01-20'),
(4, 'Standing Desk', 'Furniture', 599.99, 12, '2024-02-10'),
(5, 'Monitor', 'Electronics', 449.99, 45, '2024-01-25'),
(6, 'Bookshelf', 'Furniture', 149.99, 25, '2024-02-05');

Basic Usage with Single Columns

MIN() and MAX() handle different data types intelligently. For numeric columns, they perform mathematical comparisons. For dates, they find chronological boundaries. For strings, they use lexicographical (alphabetical) ordering.

Finding the price range across all products:

SELECT 
    MIN(price) AS lowest_price,
    MAX(price) AS highest_price,
    MAX(price) - MIN(price) AS price_range
FROM products;

Result:

lowest_price | highest_price | price_range
24.99        | 1299.99       | 1275.00

Getting the date boundaries for inventory updates:

SELECT 
    MIN(last_updated) AS first_update,
    MAX(last_updated) AS most_recent_update,
    DATEDIFF(MAX(last_updated), MIN(last_updated)) AS days_span
FROM products;

Result:

first_update | most_recent_update | days_span
2024-01-15   | 2024-02-10         | 26

String columns use alphabetical ordering, which can be useful but sometimes counterintuitive:

SELECT 
    MIN(name) AS first_alphabetically,
    MAX(name) AS last_alphabetically
FROM products;

Result:

first_alphabetically | last_alphabetically
Bookshelf           | Standing Desk

Important note on NULL values: MIN() and MAX() automatically ignore NULL values. If all values in a column are NULL, these functions return NULL.

-- NULLs are ignored
SELECT MIN(price) FROM products WHERE price IS NULL;  -- Returns NULL

Using MIN and MAX with GROUP BY

The real power of aggregate functions emerges when combined with GROUP BY. This allows you to find minimum and maximum values per category, customer, time period, or any other grouping criterion.

Finding price ranges by product category:

SELECT 
    category,
    MIN(price) AS min_price,
    MAX(price) AS max_price,
    COUNT(*) AS product_count
FROM products
GROUP BY category
ORDER BY category;

Result:

category    | min_price | max_price | product_count
Electronics | 24.99     | 1299.99   | 3
Furniture   | 149.99    | 599.99    | 3

Let’s create an orders table to demonstrate more complex grouping scenarios:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2)
);

INSERT INTO orders VALUES
(1, 101, '2024-01-10', 450.00),
(2, 102, '2024-01-15', 125.50),
(3, 101, '2024-01-20', 890.00),
(4, 103, '2024-01-25', 75.00),
(5, 102, '2024-02-01', 320.00),
(6, 101, '2024-02-05', 210.00);

Finding each customer’s order range:

SELECT 
    customer_id,
    COUNT(*) AS total_orders,
    MIN(order_date) AS first_order,
    MAX(order_date) AS last_order,
    MIN(total_amount) AS smallest_order,
    MAX(total_amount) AS largest_order,
    AVG(total_amount) AS avg_order
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Result:

customer_id | total_orders | first_order | last_order | smallest_order | largest_order | avg_order
101         | 3            | 2024-01-10  | 2024-02-05 | 210.00         | 890.00        | 516.67
102         | 2            | 2024-01-15  | 2024-02-01 | 125.50         | 320.00        | 222.75
103         | 1            | 2024-01-25  | 2024-01-25 | 75.00          | 75.00         | 75.00

Combining with WHERE and HAVING Clauses

WHERE filters rows before aggregation, while HAVING filters groups after aggregation. Understanding this distinction is crucial for writing efficient queries.

Using WHERE to filter before aggregation:

-- Find price range only for Electronics
SELECT 
    MIN(price) AS min_price,
    MAX(price) AS max_price
FROM products
WHERE category = 'Electronics';

Using HAVING to filter aggregated results:

-- Find categories where the price range exceeds $1000
SELECT 
    category,
    MIN(price) AS min_price,
    MAX(price) AS max_price,
    MAX(price) - MIN(price) AS price_range
FROM products
GROUP BY category
HAVING MAX(price) - MIN(price) > 1000;

Result:

category    | min_price | max_price | price_range
Electronics | 24.99     | 1299.99   | 1275.00

Combining both WHERE and HAVING:

-- Find categories with in-stock products where max price > $400
SELECT 
    category,
    MAX(price) AS max_price,
    SUM(stock_quantity) AS total_stock
FROM products
WHERE stock_quantity > 0
GROUP BY category
HAVING MAX(price) > 400
ORDER BY max_price DESC;

Advanced Techniques: Subqueries and JOINs

A common requirement is finding the entire row that contains the minimum or maximum value, not just the value itself. This requires subqueries or self-joins because you can’t mix aggregate and non-aggregate columns without grouping.

Wrong approach (will error):

-- This doesn't work!
SELECT name, MIN(price) FROM products;

Correct approach using a subquery:

-- Find the product with the lowest price
SELECT *
FROM products
WHERE price = (SELECT MIN(price) FROM products);

Finding products with the maximum price in each category:

SELECT p.*
FROM products p
INNER JOIN (
    SELECT category, MAX(price) AS max_price
    FROM products
    GROUP BY category
) max_prices ON p.category = max_prices.category 
              AND p.price = max_prices.max_price;

Finding customers with their highest order amount:

SELECT o.*
FROM orders o
INNER JOIN (
    SELECT customer_id, MAX(total_amount) AS max_amount
    FROM orders
    GROUP BY customer_id
) max_orders ON o.customer_id = max_orders.customer_id 
              AND o.total_amount = max_orders.max_amount;

Result:

order_id | customer_id | order_date  | total_amount
3        | 101         | 2024-01-20  | 890.00
5        | 102         | 2024-02-01  | 320.00
4        | 103         | 2024-01-25  | 75.00

Performance Considerations and Best Practices

MIN() and MAX() queries can be extremely fast when properly indexed. MySQL can often retrieve these values by reading just the first or last entry in an index without scanning the entire table.

Check query performance with EXPLAIN:

EXPLAIN SELECT MIN(price), MAX(price) FROM products;

For optimal performance, create an index on columns frequently used with MIN/MAX:

CREATE INDEX idx_price ON products(price);
CREATE INDEX idx_category_price ON products(category, price);

Alternative approach for single-row retrieval:

When you need the entire row with the min/max value, using ORDER BY with LIMIT 1 is often faster than a subquery:

-- Fastest way to get the cheapest product
SELECT *
FROM products
ORDER BY price ASC
LIMIT 1;

-- Fastest way to get the most expensive product
SELECT *
FROM products
ORDER BY price DESC
LIMIT 1;

This approach leverages indexes efficiently and avoids the subquery overhead. However, it only works when you need one result, not when grouping by categories.

Common pitfalls to avoid:

1. Don’t mix aggregates with non-aggregates without GROUP BY - Always use subqueries or joins to get full row details.

2. Remember NULL handling - MIN/MAX ignore NULLs, which might not match your expectations.

3. Be careful with string comparisons - MIN/MAX on strings uses lexicographical ordering, which may not match natural sorting (e.g., “10” comes before “2”).

4. Index appropriately - Composite indexes should have the GROUP BY column first, then the MIN/MAX column.

Conclusion

MIN() and MAX() are essential tools for boundary analysis and range queries in MySQL. Use them with single columns for overall ranges, combine them with GROUP BY for per-category analysis, and leverage subqueries or ORDER BY with LIMIT when you need complete row data.

For best performance, ensure relevant columns are indexed, especially when grouping. Remember that MIN/MAX ignore NULL values and work across different data types with type-appropriate comparisons. When you need just the aggregate value, use MIN/MAX directly. When you need the full row, consider ORDER BY with LIMIT as a faster alternative to subqueries.

Master these patterns, and you’ll handle most analytical queries efficiently and elegantly.