MariaDB min() Function

Summary: in this tutorial, you will learn how to use the MariaDB min() function to find the minimum value in a set.

Introduction to MariaDB min() function

The min() is an aggregate function that returns the minimum value in a set.

Here is the syntax of the min() function:

max(expression) Code language: SQL (Structured Query Language) (sql)

Note that the min() function returns null if the set of input values is empty. Like the max() function, the min() function ignores null values.

MariaDB min() function examples

We’ll use the tables countries and regions in the sample database to demonstrate the min() function:

A) Using MariaDB min() function to get the minimum value example

The following example uses the min() function to get the smallest area of all countries:

select min(area) from countries; Code language: SQL (Structured Query Language) (sql)

To get the full information of countries that have the smallest areas, you use this query:

select name, area from countries where area = ( select min(area) from countries ); Code language: SQL (Structured Query Language) (sql)

B) Using MariaDB min() function with a group by clause example

The min() function is often used with a group by clause to return the minimum value for each group.

For example, the following statement uses the min() function with the group by clause to find the smallest countries in each region:

select region_id, min(area) from countries group by region_id; Code language: SQL (Structured Query Language) (sql)

In this example:

• First, the group by clause divides the countries by regions into groups.
• Second, the min() function returns the smallest area for each group.

To include the region name, you can join the table countries with the table regions as shown in the following query:

select regions.name region, min(area) from countries inner join regions using (region_id) group by regions.name order by region; Code language: SQL (Structured Query Language) (sql)

C) Using MariaDB min() function with a having clause example

For example, the following statement finds all regions whose largest countries have areas greater less than 100,000 km2:

select regions.name region, min(area) from countries inner join regions using (region_id) group by regions.name having min(area) < 100000 order by region; Code language: SQL (Structured Query Language) (sql)

In this tutorial, you learned how to use the MariaDB min() function to find the maximum value in a set of values.