SQL MIN(): Find the Smallest Value (Numbers, Dates and Text)

MIN returns the smallest value in a column. It works on numbers (lowest price), dates (earliest order), and even text (first alphabetically).

The example table#

Syntax#

SELECT MIN(column)
FROM table_name
WHERE condition;

Lowest number#

SELECT MIN(salary) AS lowest
FROM employees;

lowest
------
46000

Earliest date#

For dates, "smallest" means earliest:

SELECT MIN(hire_date) AS first_hire
FROM employees;

first_hire
----------
2018-01-10

First text value alphabetically#

For text, MIN returns the value that sorts first:

SELECT MIN(name) AS first_name
FROM employees;

first_name
----------
Asha

How letters sort (especially upper vs lower case and accented characters) depends on the database's collation settings.

MIN per group#

The lowest salary in each department:

SELECT department, MIN(salary) AS lowest
FROM employees
GROUP BY department
ORDER BY department;

department  | lowest
------------+-------
Engineering | 78000
Marketing   | 46000
Sales       | 52000

Who has the minimum?#

MIN returns only the value, not who it belongs to. A common beginner attempt fails:

-- Error in most databases: name is not aggregated or grouped
SELECT name, MIN(salary) FROM employees;

Find the minimum first, then the rows that have it:

SELECT name, salary
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);

name | salary
-----+-------
John | 46000

If two employees shared the lowest salary, both would be returned, which is usually what you want. (ORDER BY salary LIMIT 1 is an alternative, but it silently picks one row when there is a tie.)

NULL values#

MIN ignores NULLs. If every value is NULL, or no rows match, MIN returns NULL.

Common mistakes#

Practice#

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

category    | cheapest
------------+---------
Stationery  | 60
Electronics | 2500
Furniture   | 4500

Key takeaway#

MAX works exactly the same way in the other direction; see MAX.