SQL MAX Aggregate Function (original) (raw)

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 does not apply 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 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 the 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 are summarized by the GROUP BY clause.

For example, to get a department that has an 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 #

• PostgreSQL MAX function
• Oracle MAX function
• SQL Server MAX function
• MySQL MAX function
• SQLite MAX function
• Db2 MAX function
• MariaDB MAX function

Was this tutorial helpful ?