How to use MAX Function in SQL Server

In this SQL Server tutorial, you will learn about the MAX function in SQL Server, which is the aggregate function that can find the maximum value.

Then, you will understand the syntax of the MAX() function and its definition. Afterwards, you will learn to use the MAX() function with DATE and VARCHAR data types. Finally, you will understand how to use the MAX() function with the GROUP BY and HAVING clause.

Table of Contents

MAX Function in SQL Server

MAX function in SQL Server finds the maximum value from the set of values. In other words, if you have any column in your table and want to know the maximum value of that column, then the MAX() function can return the maximum value from that column.

The MAX() function benefits businesses and allows you to find the maximum salary, highest sales, oldest date, etc.

The syntax is given below.

MAX( [ ALL | DISTINCT ] expression )

Where,

MAX(): This function finds the maximum value from the columns.
expression: It is an expression and can be any numeric, datetime columns, character, and uniqueidentifier, but it can’t be the aggregate function, subqueries, and bit columns.
ALL | DISTINCT: You can use these options with the MAX() function to find the maximum value from the specified columns.
- If you use the MAX(ALL expression) like this, it applies the MAX() function to all the values of the selected columns and returns the maximum value.
- In the case of MAX(DISTINCT expression), the MAX() function is applied to the unique values of the columns and returns the maximum value.

The return type of the MAX() function is the same as the input expression.

The MAX() function does not consider null values when calculating the maximum value.
The MAX() function returns a NULL value when no row is selected.
MAX() returns the highest value in the collating sequence for character columns.

Let’s take an example and find the maximum value from the columns. Suppose you have a Product_Sales table containing the product’s sale price, shown below.

MAX Function in SQL Server ProductSales Table

Find the maximum sale price of the product in the SalePrice column. So use the below query for that.

SELECT MAX(SalePrice) ProductMaxSalePrice FROM PRODUCT_SALES;

When executing the above query, it returns the maximum sale price of the product as 999999.99 dollars. Here, you have applied the MAX() function on the SalePrice column that contains the numeric values.

This is how you can retrieve the maximum value from the table columns using the MAX function in SQL Server.

MAX Function in SQL Server with Date Data Type

MAX() function also works on the column containing the date values. For example, consider a Sales table containing the product name, sale date, and sale price, shown below.

Look at the column SaleDate that shows the sale date of each product if you want to find the most recent sale date of the product. Then, use the below query.

SELECT MAX(SaleDate) AS RecentSaleDate FROM Sales;

The MAX() function returns the most recent date from the column SaleDate. As a result, it returns with the date 2023-01-21. Thus, the MAX() function also worked on the date value.

This is how you can use the MAX function in SQL Server with date datatype.

MAX Function in SQL Server with Character Data Type

When the MAX() function is applied to the columns that contain the string value, it sorts the string values alphabetically and returns the last value from the sorted string values.

For example, let’s say you have a CustomerOrders table containing OrderID, CustomerName, and OrderDate, shown below.

MAX Function in SQL Server with Character Data Type CustomerOrders Table

Now, return the customer name that appears alphabetically in the CustomerName column of the CustomerOrders table. For that, use the below query.

SELECT MAX(CustomerName) LastCustomerNameAlphabetically 
FROM CustomerOrders;

MAX Function in SQL Server with Character Data Type

The result of the above query shows that the Customer with the name Wayne is the last alphabetically. So here, when you use the MAX() function with string values ( or character values), it first sorts those values alphabetically and returns the last string value.

This is how to use the MAX() function in SQL Server with string values.

MAX Function in SQL Server with GROUP BY Clause

The GROUP BY clause separates similar data into a group. When the MAX() function and the GROUP BY clause are used, it returns the maximum value for each group separately.

The general syntax for using the MAX() function with the GROUP BY clause is below.

SELECT column_name1, MAX(column_name2) 
FROM table_name
GROUP BY column_name1;

Where,

MAX(column_name2): It returns the maximum value of column_name2 for each group.
GROUP BY column_name1: Specify the column name you would like to use for grouping the data.

For example, you have table Sales with columns sale_id, product_id, sale_amount, and sale_date, shown below.

MAX Function in SQL Server with GROUP BY Clause Sales Table

The above table shows that each product has multiple sales records with different sales amounts. You must find each product’s maximum sale by-product or sale amount. For that, use the below query.

SELECT product_id, MAX(sale_amount) AS MaxSaleByProduct
FROM Sales
GROUP BY product_id

MAX Function in SQL Server with GROUP BY Clause

After executing the above query, it groups the sales by product_id and returns the maximum sale amount for each product. For example, the maximum sale amount for the P102 is 300.00, P103 is 310.00, etc.

First, each sale amount is grouped according to the product ID using the GROUP BY product_id. Then MAX(sale_amount) is applied to each group and returns the maximum sale amount from that group.

This is how to use the GROUP BY clause with the MAX function in SQL Server.

MAX Function in SQL Server with HAVING clause

As you know, the HAVING clause is a filter that filters the groups of data based on the aggregate functions. Here, you will filter the group created by the GROUP BY clause with certain conditions.

For example, use the same table Sales you used in the above section. So, in the above section, you returned the maximal sale amount by product. Here, you need to find the maximum sale amount for each product, but only those with less than 200.00.

So use the below query.

SELECT product_id, MAX(sale_amount) AS MaxSaleByProduct
FROM Sales
GROUP BY product_id
HAVING MAX(sale_amount) < 200;

MAX Function in SQL Server with HAVING clause

As you can see, there is only one product with a maximum sale amount of 180.00, which is less than 200.00.

First, the sale amount is grouped according to their product ID using the GROUP BY product_id. Then, the maximum sales amount is retrieved from each group using MAX(sale_amount).

After that, each group based on the product_id is filtered for the sale amount of less than 200.00 using the HAVING MAX(sale_amount) < 200.

This is how you can use the MAX() function with the HAVING clause to filter the group-based data.

Conclusion

In this SQL Server tutorial, you learned how to return the maximum value from the columns using the MAX() function in SQL Server. Also, you have applied the MAX() function on a column containing the date and string values. Ultimately, you learned how to use the MAX() function with the HAVING and GROUP BY clauses.