MariaDB max() Function

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

Introduction to MariaDB max() function

The max() is an aggregate function that returns the maximum value in a set.

The following illustrates the syntax of the max() function:

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

The max() function returns null if the set is empty. The max() function ignores null values.

MariaDB max() function examples

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

A) Using MariaDB max() function to get the highest value example

The following example uses the max() function to get the largest area of all countries:

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

To find the country that has the largest area, you use the above query as a subquery:

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

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

The max() function is often used with a group by clause to find the maximum value for each group.

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

select 
    region_id,
    max(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 max() function returns the largest areas for each group.

To get the region name instead of id, you can join the table countries with the table regions:

select 
    regions.name region,
    max(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 max() function with a having clause example

You can use the max() function in the having clause to filter groups. For example, the following statement finds all regions which have the largest regions greater than 1,000,000 km2:

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

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