MySQL MAX() Function

The MySQL MAX() function returns the maximum value among all the values ​​represented by a expression in a group.

If you need to get the minimum value, use the MIN() function.

MAX() Syntax

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

MAX(expr)

We usually 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

An expression used for aggregate operations. It can be a column name or an expression.

group_expr1, group_expr2, ...

An expression or column name to use for grouping.

Return value

The MAX(expr) function returns the maximum value among all the values ​​represented by the expr in a group.

If there are no matching rows, the MAX() function returns NULL.

MAX() Examples

We’ll demonstrate this function with a table named student_score. Let’s create the table and insert some rows firstly.

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 the rows in this 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 |
+----+------+---------+-------+

MAX() Basic Example

The following SQL statement returns the maximum score from all score.

SELECT MAX(score) from student_score;

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

MAX() and GROUP BY Example

Let’s use MAX() and GROUP BY to get the maximum score in more dimensions.

1. Query the highest score for each subject

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

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

   Here, MySQL will group all rows by subject according to GROUP BY subject, and then execute MAX(score) within each group.

2. Query the highest 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 group all rows by name according to GROUP BY name, and then execute MAX(score) within each group.