SQL Server MAX(): Get Maximum Value in a Column
In SQL Server, the
MAX() function is an aggregate function that returns the maximum value in the column or expression.
It is an aggregate function so it canbe used with the GROUP BY clause to find the maximum value in a group.
expression: It can be a constant, a table column, or function, and any combination of arithmetic, or string operators.
MAX can be used with numeric, character, uniqueidentifier, or with datetime data.
Returns the maximum value in the group of the same data type. It ignores the NULL values.
Get Maximum Value in the Column
The following query fetches the maximum
Salary from the
SELECT MAX(Salary) AS MaxSal FROM Employee;
To check the above result, use the following query to fetch all records from the
Employee table ordered by
Salary in ascending order.
SELECT * FROM Employee ORDER BY Salary;
As you can see, the maximum
Salary in the
Employee is 200000.
MAX() with String Column
MAX() function can be used on the string column. For example, the following uses the
MAX() function on the
LastName column of the
It will sort the column alphabetically and the last value will be returned.
SELECT MAX(LastName) FROM Employee;
'Troy' is the last value alphabetically and so it will be returned.
MAX() with GROUP BY Clause
In the following example, the maximum salary for each department is returned by using the GROUP BY clause. Here the GROUP BY clause groups the employees based on their department and then the maximum salary in each group is returned.
SELECT DepartmentId, MAX(Salary) AS DeptMaxSalary FROM Employee GROUP BY DepartmentId;
MAX() with HAVING Clause
MAX() function can be used with the HAVING clause as shown in the below example. Here the GROUP BY clause groups the employees according to their departments, gets the maximum salary of each department, and then the HAVING clause filters the result by returning only those departments having a maximum salary greater than 50000.
SELECT DepartmentId, Max(Salary) DeptMaxSal FROM Employee GROUP BY DepartmentID HAVING MAX (Salary) > 50000;
Get Maximum and Minimum Value
In the following example, the MIN() and MAX() functions are used on the
Salary column of the
The minimum and maximum salary in the table is returned.
SELECT MIN(Salary) AS MinSal, MAX(Salary) AS MaxSal FROM Employee;