SQL Server provides us with several aggregate functions that can be used to perform different types of calculations on a set of values, and return a single value that summarized the input data set. These SQL Server aggregate functions include AVG(), COUNT(), SUM(), MIN() and MAX().
In this article, we will go through the MAX() aggregate function usage and provide multiple examples of the MAX() function usage.
Usage
SQL MAX() aggregate function is used to return the maximum value from the provided numerical expression or the highest value in the collating sequence from the provided character expression.
Syntax
The T-SQL syntax for the MAX() aggregate function is like:
|
1 |
MAX( [ ALL | DISTINCT ] expression ) |
Where the ALL option, which is the default option, indicates that the MAX() aggregate function will be applied to all the expression values and the DISTINCT option indicates that each unique value will be considered in the aggregate function, which is not applicable for the MAX() function.
The Expression here can be passed to the MAX() aggregate function can be any numeric, character, uniqueidentifier or DateTime table column, considering that the BIT data type cannot be used with the MAX() aggregate function.
The MAX() aggregate function can also take a constant value or any arithmetic or string expression, taking into consideration that the MAX() aggregate function will ignore any NULL value in the input expression.
The value that is returned from the SQL MAX() aggregate function will be the same as the Expression data type, and the returned value will be NULL when the input expression has no rows to read from.
Examples of SQL MAX() aggregate function
For the SQL MAX() aggregate function demo purposes, let us create a new testing table that contains columns with different data types, such as INT, DECIMAL, NVARCHAR, DATETIME and BIT data types, using the CREATE TABLE T-SQL statement below:
|
1 2 3 4 5 6 7 |
CREATE TABLE MAXDemo ( ID INT IDENTITY PRIMARY KEY, EmpName NVARCHAR (100), EmpDateOfBirth datetime, EmpSalary Decimal (6,2), EmpIsActive bit ) |
Once the table is created, we will fill that table with the testing data from the AdventureWorks2017 Microsoft testing database tables, using the T-SQL script below:
|
1 2 3 4 5 6 7 8 |
INSERT INTO MAXDemo SELECT E.[FName] + E.[SeName] + E.[ThName] + E.[LName] AS EmpName, E.BirthDate AS EmpDateOfBirth, EP.Rate AS EmpSalary, E.SalariedFlag AS EmpIsActive FROM [HumanResources].[Employee] E JOIN [HumanResources].[EmployeePayHistory] EP ON E.BusinessEntityID= EP.BusinessEntityID |
The test environment is now ready with a table of 316 records.
In order to get the maximum value of the ID column, that shows the last added employee, and use it for further analysis or operations, we will use the SQL MAX() aggregate function within a SELECT statement, as in the T-SQL statement below:
|
1 2 3 |
SELECT MAX(ID) AS MaxID FROM MAXDemo GO |
And the returned value in our demo here will be 316, which is the last added employee ID, as shown below:
Another scenario where we need to get the name of the employee who is located at the end of the alphabetical order. This can be achieved by applying the MAX() aggregate function on the EmpName character column, where the MAX() function will sort the EmpName column values based on that column collation, and return the last value, as in the T-SQL statement below:
|
1 2 3 4 |
SELECT MAX(EmpName) AS LastEmp FROM MAXDemo GO |
In our case here, the SELECT statement with the SQL MAX() aggregate function will return the last employee, after sorting the employee names. You can imagine it as sorting the names descending then getting the Top 1 employee name, as shown below:
The SQL MAX() aggregate function can also be used with the DateTime data type, where it will sort the DateTime values and return the last value from the sorted results. In our scenario, we need to get the youngest employee with the nearest birthdate. To achieve that, we will pass the EmpDateOfBirth column to the MAX() aggregate function, as in the T-SQL SELECT statement below:
|
1 2 3 4 |
SELECT MAX(EmpDateOfBirth) AS YoungestEmp FROM MAXDemo GO |
The previous SELECT statement with the SQL MAX() aggregate function will sort the employees’ birthdate and return the latest birthdate value. You can imagine the MAX() aggregate function as sorting the employees’ birthdate values descending then getting the Top 1 birthdate, as shown below:
Revamping the salaries scale of the employees can be checked with different types of calculations and aggregate operations including the SQL MAX() aggregate function. This can be done by passing the EmpSalary column to the MAX() function, that will sort all employees salary values and return the largest value from the column, as in the T-SQL Select statement below:
|
1 2 3 4 |
SELECT MAX(EmpSalary) AS LargestSal FROM MAXDemo GO |
Executing the previous SELECT statement with the MAX() aggregate function will be similar to the result returned from sorting the employees’ salaries values descending and return the Top 1 salary, with the largest value, as shown below:
If we try to apply the SQL MAX () aggregate function to the EmpIsActive column, with a BIT data type, as in the T-SQL SELECT statement below:
|
1 2 3 4 |
SELECT MAX(EmpIsActive) AS MaxActive FROM MAXDemo GO |
The SELECT statement execution will fail, with an error message which shows that the data type bit cannot be used with MAX operator, where it does not make any scene to see the maximum value from a bit column, that already has only two values, 1 and 0. The error message will be like below:
The SQL MAX() aggregate function can be also used within the WHERE clause of a simple SELECT query. This can be achieved by comparing a specific column value with the result returned from a subquery that returned the maximum value of that column, as shown in the T-SQL SELECT query below:
|
1 2 3 4 |
SELECT * FROM MAXDemo WHERE EmpSalary = ( SELECT MAX(EmpSalary) FROM MAXDemo ) AND EmpIsActive = 1 |
The previous SELECT query will return all information about the employee whose salary is the largest salary within all employees salary, where it will calculate the maximum value of the employees’ salaries and compare each employee’s salary with that value, as shown below:
We can also use the SQL MAX() aggregate function to return the maximum value of a specific column with other column values, where all non-aggregated columns should be listed in the GROUP BY clause. For example, the SELECT query below will list all employees’ birthdate and salary values and beside each value, it will return the maximum value, in order to compare each value with the maximum value, as shown below:
|
1 2 3 4 5 6 7 8 |
SELECT TOP 10 ID, EmpName, EmpDateOfBirth, YoungEmp = (SELECT MAX(EmpDateOfBirth) FROM MAXDemo), EmpSalary, LargeSalary = (SELECT MAX(EmpSalary) FROM MAXDemo ) FROM MAXDemo WHERE EmpIsActive = 1 GROUP BY ID, EmpName, EmpDateOfBirth , EmpSalary ORDER BY EmpSalary DESC |
And the result, that can be easily compared together, will be as below:
We can also filter the previous query result set to return only the employees with a very large salary variation from the maximum salary values, by providing an additional condition using the SQL MAX() aggregate function within the HAVING clause, as shown below:
|
1 2 3 4 5 6 7 8 9 |
SELECT TOP 10 ID, EmpName, EmpDateOfBirth, YoungEmp = (SELECT MAX(EmpDateOfBirth) FROM MAXDemo), EmpSalary, LargeSalary = (SELECT MAX(EmpSalary) FROM MAXDemo ) FROM MAXDemo WHERE EmpIsActive = 1 GROUP BY ID, EmpName, EmpDateOfBirth , EmpSalary HAVING ((SELECT MAX(EmpSalary) FROM MAXDemo)- EmpSalary) > 100 ORDER BY EmpSalary DESC |
And the result of executing the previous query will be like:
Let us add a new column to the demo table without filling any value in that column, using the ALTER TABLE T-SQL statement below:
|
1 |
ALTER TABLE MAXDemo ADD AllowanceValue INT NULL |
After adding the new column, if we try to run the below query to get the maximum allowance value from all company employees using the SQL MAX() aggregate function:
|
1 |
SELECT MAX(AllowanceValue) As MaxAllowance FROM MAXDemo |
The returned result will be NULL, as the table has no value in that column, as shown below:
Conclusion
It is clear from these above examples, how we can use the SQL MAX() aggregate function to achieve a specific goal in different scenarios, with different data types, and within any T-SQL query.
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021