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