MySQL MIN() Function

The MySQL MIN() function returns the minimum value among all the values ​​represented by the expression.

If you need to get the maximum value, use the MAX() function.

MIN() Syntax

Here is the syntax of the MySQL MIN() function:

MIN(expr)

We usually use the MIN() function like this:

SELECT MIN(expr), ...
FROM table_name
[WHERE ...];

Or use the MIN() function with the GROUP BY clause:

SELECT MIN(expr), group_expr1, group_expr2, ...
FROM table_name
[WHERE ...]
GROUP BY group_expr1, group_expr2, ...;

Parameters

expr

An expression used for aggregate operations. It can be a column name or an expression.

group_expr1, group_expr2, ...

An expression or column name to use for grouping.

Return value

The MIN(expr) function returns the minimum value among all the values ​​represented by expr.

If there are no matching rows, the MIN() function returns NULL.

MIN() Examples

We’ll demonstrate this function with a table named student_score. Let’s create the table and insert some rows firstly.

CREATE TABLE `student_score` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `score` INT NOT NULL
);
INSERT INTO `student_score` (`name`, `subject`, `score`)
VALUES ('Tom', 'Math', 80),
    ('Tom', 'English', 90),
    ('Jim', 'Math', 84),
    ('Jim', 'English', 96),
    ('Tim', 'Math', 80),
    ('Tim', 'English', 98);

Here is the data in the table:

+----+------+---------+-------+
| id | name | subject | score |
+----+------+---------+-------+
|  1 | Tom  | Math    |    80 |
|  2 | Tom  | English |    90 |
|  3 | Jim  | Math    |    84 |
|  4 | Jim  | English |    96 |
|  5 | Tim  | Math    |    80 |
|  6 | Tim  | English |    98 |
+----+------+---------+-------+

MIN() Basic Example

The following SQL statement returns the lowest score among all scores.

SELECT MIN(score) from student_score;

+------------+
| MIN(score) |
+------------+
|         98 |
+------------+

MIN() and GROUP BY Example

Let’s use MIN() and GROUP BY to get the lowest score in more dimensions.

1. Query the minimum score for each subject

   SELECT subject, MIN(score)
   FROM student_score
   GROUP BY subject;
   

   +---------+------------+
   | subject | MIN(score) |
   +---------+------------+
   | Math    |         80 |
   | English |         90 |
   +---------+------------+

   Here, MySQL will group all rows by subject according to GROUP BY subject, and then execute MIN(score) within each group.

2. Query the lowest score for each student

   SELECT name, MIN(score)
   FROM student_score
   GROUP BY name;
   

   +------+------------+
   | name | MIN(score) |
   +------+------------+
   | Tom  |         80 |
   | Jim  |         84 |
   | Tim  |         80 |
   +------+------------+

   Here, MySQL will group all rows by name according to GROUP BY name, and then execute MIN(score) within each group.