{"id":162,"date":"2016-03-24T13:17:55","date_gmt":"2016-03-24T13:17:55","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=162"},"modified":"2025-02-04T07:04:27","modified_gmt":"2025-02-04T14:04:27","slug":"sql-max","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-aggregate-functions\/sql-max\/","title":{"rendered":"SQL MAX Function"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, you will learn how to find the maximum value in a group by using the SQL MAX<\/code> function.<\/p>\r\n\r\n\r\n\r\n

Introduction to SQL MAX function #<\/a><\/h2>\r\n\r\n\r\n\r\n

SQL provides the MAX<\/code> function that allows you to find the maximum value in a set of values.<\/p>\r\n\r\n\r\n\r\n

The following illustrates the syntax of the MAX<\/code> function:<\/p>\r\n\r\n\r\n

MAX(expression)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The MAX<\/code> function ignores NULL<\/code> values.<\/p>\r\n\r\n\r\n\r\n
SQL MAX function examples #<\/a><\/h2>\r\n\r\n\r\n\r\n
We will use the employees<\/code> table to demonstrate how the MAX<\/code> function works.<\/p>\r\n\r\n\r\n\r\n
\"employees_table\"<\/figure>\r\n\r\n\r\n\r\n
The following SELECT statement<\/a> returns the highest (maximum) salary of employees in the employees<\/code> table.<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  MAX<\/span>(salary)\r\nFROM<\/span>\r\n  employees;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
To get the employees who have the highest salary, you use a subquery as follows:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  first_name,\r\n  last_name,\r\n  salary\r\nFROM<\/span>\r\n  employees\r\nWHERE<\/span>\r\n  salary = (\r\n    SELECT<\/span>\r\n      MAX<\/span>(salary)\r\n    FROM<\/span>\r\n      employees\r\n  );<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Output:<\/p>\r\n\r\n\r\n
 first_name | last_name |  salary\r\n------------+-----------+----------\r\n Steven     | King      | 24000.00<\/code><\/span><\/pre>\r\n\r\n\r\n
The subquery returns the highest salary. The outer query gets the employees who have the salary that equals the highest salary.<\/p>\r\n\r\n\r\n\r\n
SQL MAX with GROUP BY example #<\/a><\/h3>\r\n\r\n\r\n\r\n
We usually use the MAX<\/code> function with the GROUP BY<\/a> clause to find the maximum value per group.<\/p>\r\n\r\n\r\n\r\n
For example, we can use the MAX<\/code> function to find the highest salary of the employee in each department as follows:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  department_id,\r\n  MAX<\/span>(salary) max_salary\r\nFROM<\/span>\r\n  employees\r\nGROUP<\/span> BY<\/span>\r\n  department_id\r\nORDER<\/span> BY<\/span> max_salary;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Output:<\/p>\r\n\r\n\r\n
 department_id | max_salary\r\n---------------+------------\r\n             1 |    4400.00\r\n             4 |    6500.00\r\n             5 |    8200.00\r\n             6 |    9000.00\r\n             7 |   10000.00\r\n             3 |   11000.00\r\n            11 |   12000.00\r\n            10 |   12000.00\r\n             2 |   13000.00\r\n             8 |   14000.00\r\n             9 |   24000.00<\/code><\/span><\/pre>\r\n\r\n\r\n
To include the department names in the result, we join the employees table with the departments table as follows:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  department_name,\r\n  MAX<\/span>(salary) max_salary\r\nFROM<\/span>\r\n  employees e\r\n  INNER<\/span> JOIN<\/span> departments d ON<\/span> d.department_id = e.department_id\r\nGROUP<\/span> BY<\/span>\r\n  department_name\r\nORDER<\/span> BY<\/span>\r\n  max_salary;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
 department_name  | max_salary\r\n------------------+------------\r\n Administration   |    4400.00<\/span>\r\n Human Resources  |    6500.00<\/span>\r\n Shipping         |    8200.00<\/span>\r\n IT               |    9000.00<\/span>\r\n Public<\/span> Relations |   10000.00<\/span>\r\n Purchasing       |   11000.00<\/span>\r\n Accounting       |   12000.00<\/span>\r\n Finance          |   12000.00<\/span>\r\n Marketing        |   13000.00<\/span>\r\n Sales            |   14000.00<\/span>\r\n Executive        |   24000.00<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
SQL MAX with HAVING example #<\/a><\/h3>\r\n\r\n\r\n\r\n
We use the MAX<\/code> function in the HAVING<\/a> clause to add the condition to the groups that are summarized by the GROUP BY<\/code> clause.<\/p>\r\n\r\n\r\n\r\n
For example, to get a department that has an employee whose highest salary is greater than 12000<\/code>, you use the MAX<\/code> function in the HAVING<\/code> clause as follows:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  department_name,\r\n  MAX<\/span>(salary) max_salary\r\nFROM<\/span>\r\n  employees e\r\n  INNER<\/span> JOIN<\/span> departments d ON<\/span> d.department_id = e.department_id\r\nGROUP<\/span> BY<\/span>\r\n  department_name\r\nHAVING<\/span>\r\n  MAX<\/span>(salary) > 12000<\/span>\r\nORDER<\/span> BY<\/span>\r\n  max_salary;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Output:<\/p>\r\n\r\n\r\n
 department_name | max_salary\r\n-----------------+------------\r\n Marketing       |   13000.00\r\n Sales           |   14000.00\r\n Executive       |   24000.00<\/code><\/span><\/pre>\r\n\r\n\r\n
Summary #<\/a><\/h2>\r\n\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n\r\n
Unlike the SUM<\/a>, COUNT<\/a>, and AVG<\/a> functions, the DISTINCT<\/a> option does not apply to the MAX<\/code> function.<\/p>\r\n\r\n\r\n\r\n