In this SQL tutorial, we will explore the Microsoft SQL Server aggregate function MAX() and its various use cases. The MAX() function is used to return the maximum value from a specified expression. It falls under the category of aggregate functions in SQL Server, which perform calculations on a set of values and return a single value as output. This function ignores NULL values and returns NULL if no rows are found.

Let’s start by looking at the syntax of the MAX() function:

SELECT MAX(column)
FROM table;

We can use the MAX() function in conjunction with other SQL statements to achieve different results. Let’s explore some use cases:

Use Case 1: Simple Use Case

In this use case, we will use the MAX() function to retrieve the highest or maximum value from a specific column. For example, we can use the MAX() function to find the highest invoice price stored in a column:

SELECT MAX(price) AS [HighestInvoice]
FROM OrderDetails;

The result set will display the highest invoice price, regardless of any filters applied.

Use Case 2: Use MAX() with WHERE Clause

In this use case, we will use the MAX() function in conjunction with the WHERE clause to filter the results based on specific criteria. For example, we can retrieve the highest invoice price from specific months:

SELECT MAX(price) AS [HighestInvoice]
FROM OrderDetails
WHERE InvoiceMonth IN ('May', 'June', 'July');

The result set will now show the highest invoice price from the specified months.

Use Case 3: Use MAX() with GROUP BY Clause

The MAX() function can also be used with the GROUP BY clause to group rows with the same values and return the result. For example, we can group the invoice prices by month and retrieve the highest invoice from each month:

SELECT InvoiceMonth, MAX(Price) AS [HighestInvoice]
FROM OrderDetails
GROUP BY InvoiceMonth;

The result set will display the highest invoice from each month.

Use Case 4: Use MAX() with HAVING Clause

The HAVING clause is used to apply filters on aggregate functions. In this use case, we will use the MAX() function with the HAVING clause to retrieve only those months with the highest invoice greater than $4000:

SELECT InvoiceMonth, MAX(Price) AS [HighestInvoice]
FROM OrderDetails
GROUP BY InvoiceMonth
HAVING MAX(price) > 4000;

The result set will show only the months with the highest invoice greater than $4000.

Use Case 5: Use MAX() with Other Aggregate Functions

The MAX() function can also be used in conjunction with other aggregate functions like MIN, COUNT, AVG, and SUM. For example, we can retrieve the highest invoice, lowest invoice, total invoice, number of invoices, and average invoice cost from the OrderDetails table:

SELECT      
   MAX(Price)   AS [HighestInvoice], 
   MIN(price)   AS [LowestInvoice], 
   SUM(Price)   AS [TotalInvoice],
   COUNT(Price) AS [NumberOfInvoices],
   AVG(price)   AS [AverageInvoiceCost]
FROM OrderDetails;

The result set will display the highest invoice, lowest invoice, total invoice, number of invoices, and average invoice cost.

These are just a few examples of how the SQL Server MAX() function can be used. By understanding its various use cases, you can leverage this powerful function to retrieve valuable insights from your SQL Server databases.

Article Last Updated: 2022-05-05