SQL MAX Function
The SQL MAX() is an aggregate function used to find the maximum value or the highest value of a column or expression.
Syntax
The basic syntax for the MAX function is as follows:
SELECT MAX(column_name)
FROM table_name
WHERE condition;
Or the syntax for the MAX function when used with the GROUP BY clause:
SELECT colunm1_name, column2_name, ... columnN_name, MAX(column_name)
FROM table_name
WHERE condition
GROUP BY column1_name, column2_name, ... columnN_name
Parameters:
colunm1_name, column2_name, ... columnN_name: Columns that are not encapsulated within the MAX function and must be included in theGROUP BYcaluse.column_name: This is the column from which the MAX function will be returned.
Demo Table
Let us suppose that we have a table called "Employee" with the following data:
| emp_no | name | birth_date | hire_date | salary | dept_name |
|---|---|---|---|---|---|
| 1001 | James Smith | 1991-10-23 | 2010-05-13 | 6600 | Customer Service |
| 1002 | Maria Martinez | 1993-06-13 | 2018-10-08 | 9000 | Development |
| 1003 | Andrew Rodriguez | 1994-10-25 | 2019-01-23 | 9500 | Finance |
| 1004 | Robert Hernandez | 1995-09-07 | 2019-09-08 | 9600 | Development |
| 1005 | Donna Williams | 1996-03-15 | 2012-06-06 | 7400 | Finance |
| 1006 | James Johnson | 2001-04-30 | 2014-02-18 | 8000 | Marketing |
| 1007 | Stephanie Miller | 1998-01-17 | 2009-12-12 | 8500 | Human Resources |
| 1008 | Nelson Lopez | 1999-05-26 | 2010-10-06 | 7300 | Marketing |
SQL MIN Function With Single Expression Example
The primary way to use the MAX() function is to return as a single field that calculates the MAX value.
The following SQL statement will return the maximum salary of all employees in the table "Employee".
SELECT MAX(salary) AS 'Max Salary'
FROM employee;
Here we used an sql alias for the MAX(salary) field as "Max Salary".
The above SQL statement will output the following result set:
+------------+ | Max Salary | +------------+ | 9600 | +------------+
SQL MIN Function Using SQL GROUP BY Example
It is more often in real-life situations you will need to use the MAX function with the GROUP BY clause.
The following SQL statement will output the name of each department and the maximum salary for the department.
SELECT dept_name AS 'department', MIN(salary) AS 'Max Salary'
FROM employee
GROUP BY dept_name;
The above SQL statement will output the following result set:
+------------------+------------+ | Department | Min Salary | +------------------+------------+ | Customer Service | 6600 | | Development | 9600 | | Finance | 9500 | | Human Resources | 8500 | | Marketing | 8000 | +------------------+------------+
Note: Every column that is listed in the SELECT statement and not encapsulated in the SQL aggregation functions (HERE is the MAX function) must be used in the GROUP BY clause.