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.