MySQL 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:
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:
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.
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.
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.
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:
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:
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
