# MIN

## Syntax

```sql
MIN([DISTINCT] expr)
```

## Description

Returns the minimum value of *`expr`*. `MIN()` may take a string argument, in which case it returns the minimum string value. The `DISTINCT` keyword can be used to find the minimum of the distinct values of *`expr`*, however, this produces the same result as omitting `DISTINCT`.

Note that [SET](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/set) and [ENUM](https://mariadb.com/docs/server/reference/data-types/string-data-types/enum) fields are currently compared by their string value rather than their relative position in the set, so MIN() may produce a different lowest result than ORDER BY ASC.

It is an [aggregate function](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions), and so can be used with the [GROUP BY](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/group-by) clause.

`MIN()` can be used as a [window function](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions).

`MIN()` returns `NULL` if there were no matching rows.

{% tabs %}
{% tab title="Current" %}
Not only ascending, but also [descending indexes](https://mariadb.com/docs/server/server-usage/tables/create-table#index-types) can be used to optimize `MIN`.
{% endtab %}

{% tab title="< 11.4" %}
Only ascending indexes can be used to optimize `MIN`.
{% endtab %}
{% endtabs %}

## Examples

```sql
CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT); 

INSERT INTO student VALUES 
  ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
  ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
  ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
  ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);

SELECT name, MIN(score) FROM student GROUP BY name;
+---------+------------+
| name    | MIN(score) |
+---------+------------+
| Chun    |         73 |
| Esben   |         31 |
| Kaolin  |         56 |
| Tatiana |         83 |
+---------+------------+
```

`MIN()` with a string:

```sql
SELECT MIN(name) FROM student;
+-----------+
| MIN(name) |
+-----------+
| Chun      |
+-----------+
```

Be careful to avoid this common mistake, not grouping correctly and returning mismatched data:

```sql
SELECT name,test,MIN(score) FROM student;
+------+------+------------+
| name | test | MIN(score) |
+------+------+------------+
| Chun | SQL  |         31 |
+------+------+------------+
```

Difference between `ORDER BY ASC` and `MIN()`:

```sql
CREATE TABLE student2(name CHAR(10),grade ENUM('b','c','a'));

INSERT INTO student2 VALUES('Chun','b'),('Esben','c'),('Kaolin','a');

SELECT MIN(grade) FROM student2;
+------------+
| MIN(grade) |
+------------+
| a          |
+------------+

SELECT grade FROM student2 ORDER BY grade ASC LIMIT 1;
+-------+
| grade |
+-------+
| b     |
+-------+
```

As a [window function](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions):

```sql
CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES 
    ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
    ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
    ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
    ('Tatiana', 'SQL', 87);


SELECT name, test, score, MIN(score) 
  OVER (PARTITION BY name) AS lowest_score FROM student_test;
+---------+--------+-------+--------------+
| name    | test   | score | lowest_score |
+---------+--------+-------+--------------+
| Chun    | SQL    |    75 |           73 |
| Chun    | Tuning |    73 |           73 |
| Esben   | SQL    |    43 |           31 |
| Esben   | Tuning |    31 |           31 |
| Kaolin  | SQL    |    56 |           56 |
| Kaolin  | Tuning |    88 |           56 |
| Tatiana | SQL    |    87 |           87 |
+---------+--------+-------+--------------+
```

## See Also

* [AVG](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/avg) (average)
* [MAX](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/max) (maximum)
* [SUM](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/sum) (sum total)
* [LEAST()](https://mariadb.com/docs/server/reference/sql-structure/operators/comparison-operators/least) returns the smallest value from a list.

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}