Curriculum
In this tutorial, you will learn how to find the maximum value in a group by using the SQL SUM 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.
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.
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 the salary that equals the highest salary.
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) FROM employees GROUP BY department_id;
To include the department names in the result, we join the employees table with the departments 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;
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;
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 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;