SQL MAX Aggregate Function

Summary: in this tutorial, you will learn how to find the maximum value in a group by using the SQL MAX function.

Introduction to SQL MAX function #

SQL provides the MAX function that allows you to find the maximum value in a set of values.

The following illustrates the syntax of the MAX function:

MAX(expression)Code language: SQL (Structured Query Language) (sql)

The MAX function ignores NULL values.

Unlike the SUM, COUNT, and AVG functions, the DISTINCT option is not applicable to the MAX function.

SQL MAX function examples #

We will use the employees table to demonstrate how the MAX function works.

The following SELECT statement returns the highest (maximum) salary of employees in the employees table.

SELECT
  MAX(salary)
FROM
  employees;Code language: SQL (Structured Query Language) (sql)

Try it

To get the employees who have the highest salary, you use a subquery as follows:

SELECT
  first_name,
  last_name,
  salary
FROM
  employees
WHERE
  salary = (
    SELECT
      MAX(salary)
    FROM
      employees
  );Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | last_name |  salary
------------+-----------+----------
 Steven     | King      | 24000.00

The subquery returns the highest salary. The outer query gets the employees who have the salary that equals the highest salary.

SQL MAX with GROUP BY example #

We usually use the MAX function in conjunction the GROUP BY clause to find the maximum value per group.

For example, we can use the MAX function to find the highest salary of employee in each department as follows:

SELECT
  department_id,
  MAX(salary) max_salary
FROM
  employees
GROUP BY
  department_id
ORDER BY max_salary;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 department_id | max_salary
---------------+------------
             1 |    4400.00
             4 |    6500.00
             5 |    8200.00
             6 |    9000.00
             7 |   10000.00
             3 |   11000.00
            11 |   12000.00
            10 |   12000.00
             2 |   13000.00
             8 |   14000.00
             9 |   24000.00

To include the department names in the result, we join the employees table with the departments table as follows:

SELECT
  department_name,
  MAX(salary) max_salary
FROM
  employees e
  INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
  department_name
ORDER BY
  max_salary;Code language: SQL (Structured Query Language) (sql)

Try it

 department_name  | max_salary
------------------+------------
 Administration   |    4400.00
 Human Resources  |    6500.00
 Shipping         |    8200.00
 IT               |    9000.00
 Public Relations |   10000.00
 Purchasing       |   11000.00
 Accounting       |   12000.00
 Finance          |   12000.00
 Marketing        |   13000.00
 Sales            |   14000.00
 Executive        |   24000.00Code language: PHP (php)

SQL MAX with HAVING example #

We use the MAX function in the HAVING clause to add the condition to the groups that summarized by the GROUP BY clause.

For example, to get the department that has employee whose highest salary is greater than 12000, you use the MAX function in the HAVING clause as follows:

SELECT
  department_name,
  MAX(salary) max_salary
FROM
  employees e
  INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
  department_name
HAVING
  MAX(salary) > 12000
ORDER BY
  max_salary;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 department_name | max_salary
-----------------+------------
 Marketing       |   13000.00
 Sales           |   14000.00
 Executive       |   24000.00

Summary #

Use the MAX function to find the maximum value in a group of values.
Use the MAX function with the GROUP BY clause to find the maximum value for each group.

Databases #

Was this tutorial helpful ?