SQL MAX

Summary: in this tutorial, you will learn how to find the maximum value in a group by using the SQL SUM 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)

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;

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

SELECT
	employee_id,
	first_name,
	last_name,
	salary
FROM
	employees
WHERE
	salary = (
		SELECT
			MAX(salary)
		FROM
			employees
	);

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

SQL MAX with GROUP BY example

We usually use the MAX function in conjunction with 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 an employee in each department as follows:

SELECT
	department_id,
	MAX(salary)
FROM
	employees
GROUP BY
	department_id;

To include the department names in the result, we join the employee's table with the department's table as follows:

SELECT
	d.department_id,
	department_name,
	MAX(salary)
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
	e.department_id;

SQL MAX with ORDER BY example

Like other aggregate functions, to sort the result set based on the result of the MAX function, we have to place the MAX function in the ORDER BY clause.

For example, the following statement returns the highest salaries of employees in each department and sorts the result set based on the highest salaries.

SELECT
	d.department_id,
	department_name,
	MAX(salary)
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
	e.department_id
ORDER BY
	MAX(salary) DESC;

SQL MAX with HAVING example

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

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

SELECT
	d.department_id,
	department_name,
	MAX(salary)
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
	e.department_id
HAVING
	MAX(salary) > 12000;

In this tutorial, we have shown you how to use the MAX function to find the maximum value in a group of values.