The MySQL MIN() function is an aggregate function that returns the minimum value from an expression.

Typically, the expression would be a range of values returned as separate rows in a column, and you can use this function to find the minimum value from the returned rows. If there are no matching rows, MIN() returns NULL.

For example, you can use this function to find out which city has the smallest population out of a list of cities.

Syntax

The syntax of MIN() goes like this:

MIN([DISTINCT] expr) [over_clause]

Where expr is the expression for which you want the minimum value.

The over_clause is an optional clause that works with window functions. Note that the over_clause can only be used if you don’t use the DISTINCT keyword.

The (optional) DISTINCT keyword can be used to eliminate duplicate values.

Basic Example

First, here’s the raw data that we’ll use in this example:

USE world;
SELECT Name, Population
FROM City
WHERE CountryCode = 'THA';

Result:

+-------------------+------------+
| Name              | Population |
+-------------------+------------+
| Bangkok           |    6320174 |
| Nonthaburi        |     292100 |
| Nakhon Ratchasima |     181400 |
| Chiang Mai        |     171100 |
| Udon Thani        |     158100 |
| Hat Yai           |     148632 |
| Khon Kaen         |     126500 |
| Pak Kret          |     126055 |
| Nakhon Sawan      |     123800 |
| Ubon Ratchathani  |     116300 |
| Songkhla          |      94900 |
| Nakhon Pathom     |      94100 |
+-------------------+------------+

We can use the MIN() function to find the city with the smallest population (i.e. the row with the smallest value in its population column).

USE world;
SELECT MIN(Population) AS 'Minimum Value'
FROM City
WHERE CountryCode = 'THA';

Result:

+---------------+
| Minimum Value |
+---------------+
|         94100 |
+---------------+

The GROUP BY Clause

We can use the GROUP BY clause to list out each district, along with the population of that district’s smallest city (by population):

USE world;
SELECT District, MIN(Population) AS 'Minimum Value'
FROM City
WHERE CountryCode = 'AUS'
GROUP BY District;

Result:

+-----------------+---------------+
| District        | Minimum Value |
+-----------------+---------------+
| New South Wales |        219761 |
| Victoria        |        125382 |
| Queensland      |         92273 |
| West Australia  |       1096829 |
| South Australia |        978100 |
| Capital Region  |        322723 |
| Tasmania        |        126118 |
+-----------------+---------------+

The ORDER BY Clause

We can also use the ORDER BY clause to specify a column with which to order by:

USE world;
SELECT District, MIN(Population) AS 'Minimum Value'
FROM City
WHERE CountryCode = 'AUS'
GROUP BY District
ORDER BY `Minimum Value` ASC;

Result:

+-----------------+---------------+
| District        | Minimum Value |
+-----------------+---------------+
| Queensland      |         92273 |
| Victoria        |        125382 |
| Tasmania        |        126118 |
| New South Wales |        219761 |
| Capital Region  |        322723 |
| South Australia |        978100 |
| West Australia  |       1096829 |
+-----------------+---------------+

This orders the results in ascending order, which lists the minimum value first.

Note that, when ordering by a multi-word alias (like `Minimum Value`), you need to use the backtick character (`) instead of the apostrophe (') to surround the two words.

Find the Minimum Character Length

The MIN() function isn’t limited to just columns with numerical data. You can also combine MIN() with other functions to return minimum values in other areas.

For example, using our sample data, we can find the value with the minimum number of characters in the City column:

SELECT MIN(CHAR_LENGTH(Name)) AS 'Minimum Character Length'
FROM city;

Result:

+--------------------------+
| Minimum Character Length |
+--------------------------+
|                        3 |
+--------------------------+

We can also see this by using the following query (which doesn’t involve the MIN() function):

SELECT Name, CHAR_LENGTH(Name) AS 'Character Length'
FROM city
ORDER BY `Character Length`
LIMIT 10;

Result:

+------+------------------+
| Name | Character Length |
+------+------------------+
| Ome  |                3 |
| Yao  |                3 |
| Qom  |                3 |
| Itu  |                3 |
| Tsu  |                3 |
| Ube  |                3 |
| Ise  |                3 |
| Uji  |                3 |
| Ede  |                3 |
| Ota  |                3 |
+------+------------------+

Seeing as multiple cities have the same character length, we can adjust this query to return only the distinct values:

SELECT DISTINCT CHAR_LENGTH(Name) AS 'Character Length'
FROM city
ORDER BY `Character Length`
LIMIT 10;

Result:

+------------------+
| Character Length |
+------------------+
|                3 |
|                4 |
|                5 |
|                6 |
|                7 |
|                8 |
|                9 |
|               10 |
|               11 |
|               12 |
+------------------+

Using an OVER Clause

As mentioned, the syntax allows for an OVER clause to be included in your queries. Basically, the OVER clause allows you to specify how to partition query rows into groups for processing by the window function.

Here’s an example:

SELECT 
    District,
    Name AS City,
    Population AS 'City Population',
    MIN(Population) OVER(PARTITION BY District) AS 'District Minimum'
FROM City
WHERE CountryCode = 'AUS'
ORDER BY `District Minimum` DESC;

Result:

+-----------------+---------------+-----------------+------------------+
| District        | City          | City Population | District Minimum |
+-----------------+---------------+-----------------+------------------+
| West Australia  | Perth         |         1096829 |          1096829 |
| South Australia | Adelaide      |          978100 |           978100 |
| Capital Region  | Canberra      |          322723 |           322723 |
| New South Wales | Sydney        |         3276207 |           219761 |
| New South Wales | Wollongong    |          219761 |           219761 |
| New South Wales | Newcastle     |          270324 |           219761 |
| New South Wales | Central Coast |          227657 |           219761 |
| Tasmania        | Hobart        |          126118 |           126118 |
| Victoria        | Melbourne     |         2865329 |           125382 |
| Victoria        | Geelong       |          125382 |           125382 |
| Queensland      | Townsville    |          109914 |            92273 |
| Queensland      | Brisbane      |         1291117 |            92273 |
| Queensland      | Cairns        |           92273 |            92273 |
| Queensland      | Gold Coast    |          311932 |            92273 |
+-----------------+---------------+-----------------+------------------+

This example partitions the rows by District, providing the minimum value for each partition (district). This allows you to see more granular data, such as each city’s population, along with the population of the smallest city (by population) in the same district.