MySQL MIN() function explained with code examples


The MySQL MIN() function is an aggregate function that you can use to find the smallest number in a table column.

The syntax of the function is as follows:

MIN(expression)

While it can accept an expression, the function only accepts a single argument, so it simply returns the literal value you passed into it:

SELECT MIN(7);

-- +--------+
-- | MIN(7) |
-- +--------+
-- |      7 |
-- +--------+

The example above is not very useful, so let’s see how to use the MIN() function to find the smallest value in a table column.

Suppose you have a students column with the following data:

+----+-------+-------+------+
| id | name  | score | age  |
+----+-------+-------+------+
|  1 | Jack  |    72 |   19 |
|  2 | Susan |    48 |   17 |
|  3 | John  |    56 |   19 |
|  4 | Fany  |    81 |   18 |
+----+-------+-------+------+

You can find the smallest value from score and age columns as follows:

SELECT MIN(score) as smallest_score, MIN(age) as smallest_age
FROM students;

-- +----------------+--------------+
-- | smallest_score | smallest_age |
-- +----------------+--------------+
-- |             48 |           17 |
-- +----------------+--------------+

With the help of a subquery, you can also use the MIN() function to find a single row with the smallest column value.

The following SELECT statement returns a single row with the smallest score value:

SELECT * FROM students 
WHERE score = (SELECT MIN(score) FROM students);

-- +----+-------+-------+------+
-- | id | name  | score | age  |
-- +----+-------+-------+------+
-- |  2 | Susan |    48 |   17 |
-- +----+-------+-------+------+

Since the MIN() function is an aggregate function, you need to use the GROUP BY clause when you need to SELECT a regular column together with the function.

Consider the following example:

SELECT age, MIN(score) as smallest_score
FROM students 
GROUP BY age;

-- +------+----------------+
-- | age  | smallest_score |
-- +------+----------------+
-- |   19 |             56 |
-- |   17 |             48 |
-- |   18 |             81 |
-- +------+----------------+

The returned result set from the query above shows the smallest score from each age group.

Finally, you can also add a normal WHERE clause to help narrow down the result of the query.

The following example returns the smallest score from students who are 19 years old:

SELECT MIN(score) as smallest_score
FROM students
WHERE age = 19;

-- +----------------+
-- | smallest_score |
-- +----------------+
-- |             56 |
-- +----------------+

And that’s how the MIN() function works in MySQL database server. You can use the function to run a query related to finding the smallest value in your database table.

Note that the MIN() function is most useful when you need to find the minimum value from a table column.

If you need to find the smallest value from a list of defined arguments, then you will need to use the LEAST() function.

Learn more: Finding the smallest value with the LEAST() function

Get 50 MySQL Snippets Book for FREE 🔥

50 MySQL snippets that you can use in various scenarios

Save 1000+ hours of research and 10x your productivity