MySQL min() Function

Last updated: October 19, 2026Oct 19, 2026 3 min read

MIN() returns the smallest value in a set: the cheapest price, the earliest date, the lowest stock. It is the mirror image of MAX() and follows the same rules.

1. Basic MIN

Table: products (6 rows)

SELECT MIN(price) AS cheapest, MIN(best_before) AS soonest_expiry, MIN(stock) AS lowest_stock FROM products;

Result: 1 row

Almonds have a NULL stock, which MIN ignores. For text, MIN returns the value that sorts first:

SELECT MIN(name) AS first_alphabetically FROM products;

Result: 1 row

2. MIN with WHERE

SELECT MIN(price) AS cheapest_in_stock FROM products WHERE stock > 0;

Result: 1 row

SELECT MIN(best_before) AS next_expiry FROM products WHERE best_before >= '2026-10-01';

Result: 1 row

3. MIN per Group

SELECT category, MIN(price) AS from_price, COUNT(*) AS items FROM products GROUP BY category ORDER BY from_price;

Result: 3 rows

4. Finding the Cheapest Product in Each Category

To return the product itself, not just the price, compare each row with its category's minimum:

SELECT p.category, p.name, p.price
FROM products p
JOIN (SELECT category, MIN(price) AS min_price FROM products GROUP BY category) m
  ON m.category = p.category AND m.min_price = p.price
ORDER BY p.category;

Result: 3 rows

Or with a window function:

SELECT category, name, price
FROM (SELECT p.*, MIN(price) OVER (PARTITION BY category) AS cat_min FROM products p) t
WHERE price = cat_min
ORDER BY category;

Result: 3 rows

5. MIN and MAX Together

SELECT category,
       MIN(price) AS lowest,
       MAX(price) AS highest,
       MAX(price) - MIN(price) AS price_range
FROM products
GROUP BY category;

Result: 3 rows

6. MIN vs LEAST

LEAST() picks the smallest of several values in the same row, for example the lower of the list price and a promotional price:

SELECT name, price,
       LEAST(price, price * 0.9, price - 100) AS best_offer
FROM products
WHERE category = 'Oils';

Result: 2 rows

7. Empty Sets Return NULL

SELECT MIN(price) AS cheapest_spice, COALESCE(MIN(price), 0) AS with_default FROM products WHERE category = 'Spices';

Result: 1 row

8. Practice Exercises

SELECT name, best_before FROM products WHERE stock > 0 ORDER BY best_before LIMIT 1;

SELECT category, MIN(price) AS from_price FROM products GROUP BY category HAVING MIN(price) > 600;

9. Summary

• MIN(col) returns the smallest non-NULL value; for dates the earliest, for text the first in sort order.
• Combine with WHERE and GROUP BY; returns NULL on an empty set.
• Join back to a MIN sub-query or use MIN() OVER to get the full row.
• LEAST compares values within one row.