Ahmad Yaseen
Max() function example 7

SQL Server MAX() aggregate function

March 19, 2020 by

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:

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:

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:

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:

And the returned value in our demo here will be 316, which is the last added employee ID, as shown below:

Max() function example 1

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:

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:

Max() function example 2

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:

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:

Max() function example 3

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:

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:

Max() function example 4

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:

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:

Max() function example 5 error

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:

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:

Max() function example 6

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:

And the result, that can be easily compared together, will be as below:

Max() function example 7

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:

And the result of executing the previous query will be like:

Max() function example 8

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:

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:

The returned result will be NULL, as the table has no value in that column, as shown below:

Max() function example 9

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.

Ahmad Yaseen
SQL commands, T-SQL

About Ahmad Yaseen

Ahmad Yaseen is a SQL Server database administration leader at Aramex International Company with a bachelor’s degree in computer engineering as well as .NET development experience. He is a Microsoft Certified Professional with a good experience in SQL server development, administration, performance tuning, monitoring and high availability and disaster recovery technologies. Also, he is contributing with his SQL tips in many blogs View all posts by Ahmad Yaseen

380 Views