MySQL MIN MAX: Smallest and Largest Values

MIN and MAX return the smallest and largest value. They work on numbers, text and dates.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

query.sql

SQL

SELECT MIN(price) AS cheapest, MAX(price) AS dearest, MIN(name) AS first_alphabetically, MAX(added_on) AS newest_product_date FROM products;

Output

OUTPUT

+----------+---------+----------------------+---------------------+
| cheapest | dearest | first_alphabetically | newest_product_date |
+----------+---------+----------------------+---------------------+
|    15.50 |  999.00 | Backpack             | 2026-04-18          |
+----------+---------+----------------------+---------------------+
1 row in set

Text is compared according to the collation, and dates chronologically. NULL values are ignored.

Getting the row behind the value

MAX(price) gives the number, not the product name:

query.sql

SQL

SELECT name, price FROM products WHERE price = (SELECT MAX(price) FROM products);

Output

OUTPUT

+----------+--------+
| name     | price  |
+----------+--------+
| Backpack | 999.00 |
+----------+--------+
1 row in set

The subquery finds the highest price, and the outer query finds the row(s) with it. If two products share the top price, you get both. For exactly one row, ORDER BY price DESC LIMIT 1 (Top-N).

Per group

query.sql

SQL

SELECT category, MIN(price) AS cheapest, MAX(price) AS dearest, MAX(price) - MIN(price) AS price_range
FROM products
GROUP BY category
ORDER BY category;

Output

OUTPUT

+------------+----------+---------+-------------+
| category   | cheapest | dearest | price_range |
+------------+----------+---------+-------------+
| bags       |   450.00 |  999.00 |      549.00 |
| home       |   220.00 |  650.00 |      430.00 |
| stationery |    15.50 |  145.00 |      129.50 |
+------------+----------+---------+-------------+
3 rows in set

Most recent row per group

“The latest product of each category” needs the row, not only the date. A join to the per-group maximum finds it:

query.sql

SQL

SELECT p.category, p.name, p.added_on
FROM products p
JOIN (SELECT category, MAX(added_on) AS last_date FROM products GROUP BY category) m
  ON m.category = p.category AND m.last_date = p.added_on
ORDER BY p.category;

Output

OUTPUT

+------------+---------------+------------+
| category   | name          | added_on   |
+------------+---------------+------------+
| bags       | Laptop Sleeve | 2026-04-02 |
| home       | Water Bottle  | 2026-03-20 |
| stationery | Sticky Notes  | 2026-04-18 |
+------------+---------------+------------+
3 rows in set

A window function is shorter (ROW_NUMBER).

Speed: MIN and MAX use indexes

On an indexed column, MIN and MAX read one index entry (the first or the last), so they are instant, even on a huge table:

query.sql

SQL

1
2
3

CREATE TABLE big_orders (id INT AUTO_INCREMENT PRIMARY KEY, placed_at DATETIME NOT NULL, KEY idx_placed (placed_at));
INSERT INTO big_orders (placed_at) VALUES ('2026-01-01 00:00:00'), ('2026-03-01 00:00:00'), ('2026-02-01 00:00:00');
EXPLAIN FORMAT=TRADITIONAL SELECT MAX(placed_at) FROM big_orders;

Output

OUTPUT

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set

Select tables optimized away means MySQL answered the query from the index alone, without reading any table rows.

Using MAX for the next number: a trap

SELECT MAX(id) + 1 as a “next id” is a classic bug: two sessions read the same maximum and create the same id. Use AUTO_INCREMENT (AUTO_INCREMENT).

MIN and MAX of text and dates

query.sql

SQL

SELECT MIN(added_on) AS oldest, MAX(added_on) AS newest, DATEDIFF(MAX(added_on), MIN(added_on)) AS days_between FROM products;

Output

OUTPUT

+------------+------------+--------------+
| oldest     | newest     | days_between |
+------------+------------+--------------+
| 2026-01-10 | 2026-04-18 |           98 |
+------------+------------+--------------+
1 row in set

MIN and MAX of several columns in a row

MIN and MAX work down the rows. To compare values across columns of one row, use LEAST and GREATEST (Numeric Functions).

warning

Selecting a name with MAX

SELECT name, MAX(price) FROM products is rejected in the default mode. In old MySQL it silently returned an arbitrary name. Use a subquery, a join, a window function or ORDER BY ... LIMIT 1.