MAX() Function

The MySQL MAX() function is used to return the maximum value in a set of values of an expression. This aggregate function is useful when we need to find the maximum number, selecting the most expensive product, or getting the largest payment to the customer from your table.

Syntax

The following is the basic syntax of MAX() function in MySQL:

SELECT MAX(DISTINCT aggregate_expression)    

FROM table_name(s)    

[WHERE conditions]; 

Parameter Explanation

This function uses the following parameters:

aggregate_expression: It is the required expression. It specifies the column, expression, or formula from which the maximum value will be returned.

table_name(s): It specifies the tables from where we want to retrieve records. There must be at least one table listed in the FROM clause.

WHERE conditions: It is optional. It specifies the conditions that must be fulfilled for the records to be selected.

DISTINCT: It allows us to return the maximum of the distinct values in the expression. However, it does not affect the MAX() function and produces the same result without using this keyword.

MySQL MAX() Function Example

Let us understand how the MAX function works in MySQL with the help of various examples. Consider our database has a table named “employees” that contains the following data.

1. Basic Example

Execute the following query that uses the MAX function to find the maximum income of the employee available in the table:

mysql> SELECT MAX(income) AS "Maximum Income" FROM employees;  

Output

The above query produces the result of maximum values in all rows. After execution, we will get the output as below:

2. MySQL MAX() Function with WHERE Clause

The WHERE clause allows us to filter the result from the selected records. The following statement finds the maximum income in all rows from the employee table. The WHERE clause specifies all those rows whose emp_age column is greater than 35.

mysql> SELECT MAX(income) AS "Maximum_Income"   

FROM employees   

WHERE emp_age > 35;  

Output

The above statement will get the output as below:

3. MySQL MAX() Function with GROUP BY Clause

The GROUP BY clause allows us to collect data from multiple rows and group it based on one or more columns. For example, the following statement uses the MAX() function with the GROUP BY clause to find the maximum income in all rows from the employee table for each emp_age group.

  mysql> SELECT emp_age, MAX(income) AS "Maximum Income"   

FROM employees   

GROUP BY emp_age;  

Output

After the successful execution, we can see that the maximum income of the employee returns by grouping them based on their age:

3. MySQL MAX() Function with HAVING Clause

The HAVING clause is always used with the GROUP BY clause to filter the records from the table. For example, the following statement returns the maximum income among all employees, grouping them based on their city and returns the result whose MAX(income) >= 200000.

mysql> SELECT city, MAX(income) AS "Maximum Income"   

FROM employees   

GROUP BY city  

HAVING MAX(income) >= 200000; 

Output

This statement will return the output as below:

5. MySQL MAX() Function with DISTINCT Clause

MySQL uses the DISTINCT keyword to remove the duplicate rows from the column name. We can also use this clause with MAX() function to return the maximum income value of a unique number of records present in the table.

Execute the following query that removes the duplicate records in the employee table’s income column, group by city, and then returns the maximum value:

mysql> SELECT city, MAX(DISTINCT income) AS "Maximum Income"   

FROM employees   

GROUP BY city;

Output

This statement will give the output as below:

6. MySQL MAX() Function in Subquery Example

Sometimes it is required to use the subquery for returning the maximum value in the table. In that case, we use the following query:

 mysql> SELECT * FROM employees WHERE   

emp_age = (SELECT MAX(emp_age) FROM employees); 

The subquery first finds the maximum age of employees from the table. Then, the main query (outer query) returns the result of age being equal to the maximum age returned from the subquery and other information.

Output