SQL MAX() function

The MAX() function is an aggregate function that is used to find the largest value in the given column or expression. It can be applied on the numeric, character, or date values.

Syntax:

SELECT MAX(column_name)
FROM table_name
[WHERE condition]
[GROUP BY];

For the demo purpose, we will use the following Employee and Department tables in all examples.

EmpId FirstName LastName Email Salary DeptId
1 John King '[email protected]' 24000 10
2 James Bond 60000 20
3 Neena Kochhar '[email protected]' 15000 20
4 Lex De Haan '[email protected]' 9000 30
5 Amit Patel 60000 30
6 Abdul Kalam '[email protected]' 4800 40

The following selects the maximum salary from the Employee table.

SQL Script: MAX()
SELECT MAX(Salary) AS "Highest Salary" FROM Employees;
Highest Salary
60000

The following query gets all employees whose salary is maximum.

SQL Script: MAX()
SELECT * FROM Employee 
WHERE Salary = (SELECT MAX(Salary) FROM Employee);
EmpId FirstName LastName Email Salary DeptId
5 Amit Patel 60000 30
2 James Bond 60000 20

The MAX() is an aggregate function, so it can be used in Group By queries. The following query gets highest salary in each department.

SQL Script: MAX() with Group By
SELECT DeptId, MAX(Salary) AS "Highest Salary" 
FROM Employee
GROUP BY DeptId;
DeptId Highest Salary
10 24000
20 60000
30 60000
40 4800

The MAX() function can be allpied on the varchar columns. The following selects the largest FirstName from the Employee table.

SQL Script: MAX() with varchar column
SELECT MAX(FirstName) AS "Largest FirstName" FROM Employee;
Largest FirstName
James