MySQL max function

Last updated on December 13, 2015

In this tutorial you will learn how to get maximum value from the specified column of table using MySQL’s max() function. We can easily use max function with select statement. You can also use Max function with the Having and group by clauses.

syntax of Max function

    
   // simple query
    Select max(column_name) from table_name
 
   // with where condition
    Select max(column_name) from table_name where x_column_name = value;
 
   // with group by
   select x_column_name,max(column_name) from table_name group by x_column_name
 
   // with HAVING
   SELECT x_column_name, MAX(column_name) FROM table_name GROUP BY x_column_name HAVING MAX(column_name) > 1000;

Mysql – Max() function examples

For example if we have a table called emp_salary with id,name,salary columns. This table stores salaries of employees with their names.

To get the height salary in from ’emp_salary’ table, you use the following query:

SELECT MAX(salary) FROM emp_salary;

To get the Nth highest salary from ’emp_salary’ table , you use the following query:

 SELECT DISTINCT(salary) FROM emp_salary ORDER BY salary DESC LIMIT n,1 

n would be the nth item you would want to return

MySQL MAX with GROUP BY – Example

For example lets assume if you want to get maximum take home salaries of each employee in the overall year along with name. to get the required data you use the MAX function with the GROUP BY clause in a SELECT statement, as below shown:

SELECT name, MAX(salary) FROM emp_salary GROUP BY name ORDER BY MAX(salary) where date >= DATE_SUB(NOW(),INTERVAL 1 YEAR);

MySQL MAX with HAVING

The MySQL HAVING clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE. Let assume that if you want to get the employee name along with maximum drowns salaries are greater than ‘10000’ rupees.

SELECT name, MAX(salary) FROM emp_salary GROUP BY emp_salary HAVING MAX(salary) > 10000;