Problem
In this SQL tutorial, I will explain the Microsoft SQL Server aggregate function MAX() and its various use cases. Read this SQL tutorial to understand the way we can use it with other SQL statements.
Solution
The SQL MAX() function is used to return the maximum value from the specified expression. It is categorized under aggregate functions in SQL Server. Aggregate functions execute calculations on a set of values from a specified expression and return a single value in their output. This function ignores NULL values from specified columns and returns NULL if the output will not find any rows.
Below is the syntax of this SQL function:
SELECT MAX(column)
FROM table
We can also use SQL MAX with various other SQL database statements which I will describe to you with the help of use cases in the below section.
Use cases of SQL MAX statement
I have two tables, “Associates” and “OrderDetails“. Table “Associates” stores information about sales associates and the “OrderDetails” table stores information on each order. I will use these two tables in the below use cases for this demonstration. Feel free to run the T-SQL SELECT statements in this tutorial on your set of tables. Make sure to change the table and column names from these SQL queries.
This section will describe the use cases of the SQL MAX statement as listed below:
- Generic or simple use case
- Use MAX statement with SQL WHERE clause
- Use MAX statement with SQL GROUP BY clause
- Use MAX statement with SQL ORDER BY clause
- Use MAX statement with SQL HAVING clause
- Use MAX statement with other SQL aggregate functions
Simple use case
As I stated above, this function will return the maximum value from the specific column. Here, I will show you how to use this function to get the highest or maximum value. I will use the table “OrderDetails” to list the highest invoice price stored in the column.
I will pass the Price column in the SQL MAX function as I have used in the below query to get our output.
SELECT MAX(price) AS [HighestInvoice]
FROM OrderDetails
GO
We can see our result set has returned the largest value invoice which is $9200.
We can see the output has returned the maximum value for the Invoice irrespective of any filter. If we want to get the maximum invoice price from specific months, then we can apply a filter using the WHERE clause in the above query and get the result. Let’s assume we have been tasked to get the highest invoice value or price from specified months like I have chosen from May, June, and July in the following query.
SELECT MAX(price) AS [HighestInvoice]
FROM OrderDetails
WHERE InvoiceMonth IN ('May', 'June', 'July')
GO
The highest invoice price is now showing as $8999 in the result set whereas it was $9200 in the above screenshot. This is because I have pulled maximum invoice data from only 3 months May, June, July that is specified in the above query.
We have learned how to use the SQL MAX function in a basic scenario. Let’s use it along with other SQL clauses in the next use cases of the below sections.
SQL MAX Function Examples with the WHERE Clause
This section will help you understand how to use the SQL MAX function in the WHERE clause. Let’s assume you want to list out product details of the highest invoice along with other details like the month in which that invoice was generated. We will get this done using the SQL MAX function in the WHERE clause as given in the below query.
SELECT Product, InvoiceMonth, Price
FROM OrderDetails
WHERE price = (SELECT MAX(Price) from OrderDetails)
GO
Let’s understand the result set returned by the above query. We can see the highest invoice of $9200 which was returned in the above section as well as generated in the month of August along with its product for which invoice was generated in the below screenshot.
We can also use SQL JOINs in the above query to get other useful information from other tables like I have used JOIN on the second table Associates to list out which associate has created this invoice along with other details from that table.
SELECT name, od.City, Designation, InvoiceMonth, Price, Product
FROM Associates AS a
JOIN OrderDetails AS od ON a.ID=od.PersonID
WHERE price = (SELECT MAX(Price) from OrderDetails)
GO
You can see the result set of the above query. We got more details about our highest invoice.
Here, we have learned the use of the SQL MAX function in the WHERE clause and with SQL JOINs.
Use SQL MAX function with GROUP BY statement
The SQL MAX function can also be used with the GROUP BY statement. Let me show you a use case to make you understand its uses. The GROUP BY statement groups rows that have the same values from the specified column and return the result. Here, I have grouped InvoiceMonth to list out the highest invoice from each month.
SELECT InvoiceMonth, MAX(Price) AS [HighestInvoice]
FROM OrderDetails
GROUP BY InvoiceMonth
GO
The below output is showing the result set in which we can see the highest invoice from each month.
We can also apply filters with the help of the WHERE clause in the above query. If you want to fetch the highest invoice data for only specific months then you can use the WHERE clause as I have shown in the below query to return the highest invoice from June and July months.
SELECT InvoiceMonth, MAX(Price) AS [HighestInvoice]
FROM OrderDetails
WHERE InvoiceMonth IN ('June', 'July')
GROUP BY InvoiceMonth
GO
We have learned how to use GROUP BY statement with SQL MAX function in this section.
Use SQL MAX function with GROUP BY and ORDER BY statement
We can use the ORDER BY statement to display the result in an order. I have just added a line of code for the ORDER BY statement in the above query to list out results in a specific order.
SELECT InvoiceMonth, MAX(Price) AS [HighestInvoice]
FROM OrderDetails
GROUP BY InvoiceMonth
ORDER BY MAX(price)
GO
Have a look at the result set of the above query.
This section has explained how to use GROUP BY and ORDER BY statements with SQL MAX function to list out highest invoice details for each month.
Use SQL MAX function with HAVING statement
The SQL HAVING statement is used if we need to apply filters on aggregate functions. Let’s understand its uses with the help of the below example. The below query will list only those months which have the highest invoice as more than $4000. I have just added one line of code for the HAVING statement.
SELECT InvoiceMonth, MAX(Price) AS [HighestInvoice]
FROM OrderDetails
GROUP BY InvoiceMonth
HAVING MAX(price) > 4000
GO
Here is its result set in which we can see only 2 rows for Aug and July. It means we have only these months in which the highest invoice is generated more than $4000.
We can use any arithmetic operator with the HAVING statement to filter result as per desired need.
Use it with other aggregate functions like MIN, COUNT AVG, SUM
The SQL MAX function can also be used with other aggregate functions like MIN, COUNT, AVG, SUM, etc.
- SQL MIN – This function will return the lowest or minimum value from the specified column.
- SQL COUNT– This function will return the total count of the specified expression.
- SQL AVG – We can get the average value of a specified column or expression using the SQL function AVG().
- SQL SUM – If you want to get the sum of all values of a specified column or expression then you can use SQL function SUM().
Let me show you a use case in which I will use all the above aggregate functions to return their respective values as shown below.
SELECT
MAX(Price) AS [HighestInvoice],
MIN(price) AS [LowestInvoice],
SUM(Price) AS [Total Invoice],
COUNT(Price) AS [Number of Invoice],
AVG(price) AS [AverageInvoiceCost],
FROM OrderDetails
GO
Have a look at the result set of the above query in which I have returned the highest invoice, lowest invoice, total invoice, number of processed invoices, and average invoice cost of the table OrderDetais.
If you want to see a similar view for each month then we can use the GROUP BY statement in the above query and get our desired output. See the below queries which will display all above outputs for each month from May to Aug.
SELECT
InvoiceMonth,
MAX(Price) AS [HighestInvoice],
MIN(price) AS [LowestInvoice],
SUM(Price) AS [Total Invoice],
COUNT(Price) AS [Number of Invoice],
AVG(price) AS [AverageInvoiceCost],
FROM OrderDetails
GROUP BY InvoiceMonth
GO
You can easily correlate the result set to understand the query and its output by looking at the below image.
This section has explained how to use other aggregate functions with the SQL MAX function.
Next Steps
- This article has described the SQL MAX function and its use cases to return the different sets of results. This function is used to display the maximum number from a specified column. You can also use this function with various SQL statements and with other aggregate functions like MIN, AVG, SUM, and COUNT.
- Additional Resources
