MySQL MIN() function explained with code examples

Posted on Dec 01, 2021

Learn how MySQL MIN function works 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

Level up your programming skills

I'm sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I'll send new stuff straight into your inbox!

No spam. Unsubscribe anytime.