T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL MAX() Function



The T-SQL (Transact-SQL) MAX() function returns the maximum value of an expression or specified column of a given table.

Syntax

The syntax for using MAX() function is given below:

SELECT MAX(column_name) 
FROM table_name
WHERE condition(s);

The T-SQL (Transact-SQL) GROUP BY clause is used to arrange result table into identical groups when one or more columns are used. Please note that it is must to include those column names in a GROUP BY clause which are not encapsulated within the MAX() function. See the syntax below:

SELECT column1, column2, ...
       MAX(column_name) 
FROM table_name
WHERE condition(s)
GROUP BY column1, column2, ...;

Parameters

column1, column2, ... Specify the column names that are not encapsulated within the MAX() function. It must be included in the GROUP BY clause.
column_name Specify the column or expression from which the maximum value need to be returned.
table_name Specify the table name from where the records need to retrieved.
WHERE condition(s) Optional. Specify the condition(s). Records are selected based upon specified condition(s).

Return Value

Returns the maximum value of a given expression.

Example - With Single Column

Consider a database table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

To fetch maximum Salary of the employees present in the Employee table, the query will be:

SELECT MAX(Salary) AS MaxSalary 
FROM Employee;

This result of the above query will be:

MaxSalary
3100


Example - Using GROUP BY

Consider the above discussed database named Employee which contains the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

To fetch the maximum Salary of the employees group by their age, where age is greater than 27, the following query can be used:

SELECT Age, MAX(Salary) AS MaxSalary 
FROM Employee
WHERE Age > 27
GROUP BY Age;

This result of the above code will be:

AgeMaxSalary
283000
303100

❮ T-SQL Functions