MIN()

Syntax

How it works

MIN() returns the smallest value in a group. It is one of the five core SQL aggregate functions, alongside MAX(), COUNT(), SUM() and AVG(), and it collapses many rows into a single answer: the minimum.

It is not limited to numbers. On text columns MIN() returns the value that sorts first alphabetically (subject to the column collation), and on date or timestamp columns it returns the earliest point in time. This makes it handy for questions like the cheapest product, the first name in a sorted list, or the date of a customer first order.

Like every aggregate, MIN() ignores NULL values and is normally paired with GROUP BY to produce one minimum per group. Since SQL:2003 it can also be used as a window function with an OVER() clause, which keeps every row instead of collapsing them. For the full family see the aggregate functions guide.

Examples

Smallest numeric value

SELECT MIN(price) AS cheapest
FROM products;

cheapest
--------
    4.99

Earliest date

-- when did this customer first order?
SELECT MIN(order_date) AS first_order
FROM orders
WHERE customer_id = 42;

first_order
-----------
2025-03-11

Alphabetically first string

SELECT MIN(name) AS first_name
FROM customers;

first_name
----------
Aisha

Minimum per group with GROUP BY

SELECT category, MIN(price) AS cheapest
FROM products
GROUP BY category
ORDER BY cheapest;

category | cheapest
---------+---------
 Books   |     4.99
 Toys    |     8.50
 Phones  |   199.00

MIN as a window function (keeps every row)

SELECT name, category, price,
       MIN(price) OVER (PARTITION BY category) AS category_min
FROM products
ORDER BY category, price;

name    | category | price | category_min
--------+----------+-------+-------------
 Atlas  | Books    |  4.99 |         4.99
 Novel  | Books    | 12.00 |         4.99
 Drone  | Toys     |  8.50 |         8.50

Filter groups on the minimum with HAVING

-- categories whose cheapest item is still over 50
SELECT category, MIN(price) AS cheapest
FROM products
GROUP BY category
HAVING MIN(price) > 50;

category | cheapest
---------+---------
 Phones  |   199.00
 Laptops |   450.00

Common mistakes

-- WRONG: name is not from the cheapest row.
-- MIN(price) and MIN(name) are computed separately,
-- so this pairs the smallest price with the
-- alphabetically first name, not the same row.
SELECT MIN(name) AS name, MIN(price) AS price
FROM products;

-- Get the actual cheapest product row
SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 1;

-- Aggregates are not allowed in WHERE
SELECT category, MIN(price)
FROM products
WHERE MIN(price) > 50
GROUP BY category;

SELECT category, MIN(price)
FROM products
GROUP BY category
HAVING MIN(price) > 50;

-- Returns NULL, which can surprise downstream maths
SELECT MIN(price) FROM products WHERE category = 'unknown';

-- Provide a fallback when the group may be empty
SELECT COALESCE(MIN(price), 0) FROM products WHERE category = 'unknown';

Performance

MIN() on an unindexed column is a single full pass: the engine scans every row to find the smallest value. That cost grows linearly with the number of rows.

An index on the column changes the game. Because a B-tree index keeps values in sorted order, the smallest value is simply the first leaf entry, so a bare SELECT MIN(indexed_col) can be answered in O(log n) time by walking to the left-most entry instead of reading the table. The same trick is why ORDER BY col LIMIT 1 is also fast on an indexed column.

With GROUP BY, an index on (group_col, value_col) lets many engines do a loose or skip scan, jumping to the minimum within each group rather than sorting the whole table. See the indexing guide for how these index-only scans work.

Interview questions

SELECT name, category, price
FROM (
  SELECT name, category, price,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY price ASC) AS rn
  FROM products
) t
WHERE rn = 1;