MariaDB MAX() Function

In MariaDB, MAX() is a built-in function that returns the maximum value among all values represented by a given expression.

MariaDB `MAX()` Syntax

Here is the syntax of the MariaDB MAX() function:

MAX(expr)

We typically use the MAX() function like this:

SELECT MAX(expr), ...
FROM table_name
[WHERE ...];

Or use the MAX() function with the GROUP BY clause:

SELECT MAX(expr), group_expr1, group_expr2, ...
FROM table_name
[WHERE ...]
GROUP BY group_expr1, group_expr2, ...;

Parameters

expr: The expr expression used for aggregation operations. It can be a column name or an expression.
group_expr1, group_expr2, ...: The expression or column name used for grouping.

Return value

The MariaDB MAX(expr) function returns the maximum value among all the values represented by the expression.

If there is no matching row, the MAX() function returns NULL.

MariaDB `MAX()` Examples

We’ll demonstrate with the student_score table. Let’s first create a demo table and insert test rows.

CREATE TABLE `student_score` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `score` INT NOT NULL
);
INSERT INTO `student_score` (`name`, `subject`, `score`)
VALUES ('Tom', 'Math', 80),
    ('Tom', 'English', 90),
    ('Jim', 'Math', 84),
    ('Jim', 'English', 96),
    ('Tim', 'Math', 80),
    ('Tim', 'English', 98);

Here is all the rows in the table:

+----+------+---------+-------+
| id | name | subject | score |
+----+------+---------+-------+
|  1 | Tom  | Math    |    80 |
|  2 | Tom  | English |    90 |
|  3 | Jim  | Math    |    84 |
|  4 | Jim  | English |    96 |
|  5 | Tim  | Math    |    80 |
|  6 | Tim  | English |    98 |
+----+------+---------+-------+

Basic example

The following SQL statement returns the best grade among all grades.

SELECT MAX(score) from student_score;

+------------+
| MAX(score) |
+------------+
|         98 |
+------------+

`GROUP BY`

Let’s use MAX() and GROUP BY to see the top score for more dimensions.

Query the highest score for each subject

SELECT subject, MAX(score)
FROM student_score
GROUP BY subject;

Output:

+---------+------------+
| subject | MAX(score) |
+---------+------------+
| Math    |         84 |
| English |         98 |
+---------+------------+

Here, MySQL will first group the result set by subject according to GROUP BY subject, and then execute MAX(score) within each group.

Query best score for each student

SELECT name, MAX(score)
FROM student_score
GROUP BY name;

+------+------------+
| name | MAX(score) |
+------+------------+
| Tom  |         90 |
| Jim  |         96 |
| Tim  |         98 |
+------+------------+

Here, MySQL will first group the result set by name according to GROUP BY name, and then execute within each group MAX(score).

Conclusion

In MariaDB, MAX() is a built-in function that returns the maximum value among all values represented by a given expression.

MariaDB MAX() Syntax