SQL MAX Function

The SQL Server MAX function is a part of the aggregate functions that finds the maximum value from the total rows or records selected by the SELECT Statement. When performing the data analysis, it helps to identify the highest or largest value across the dataset or a particular group. Apart from that, it is crucial to track the highest sales, financial analysis, employee performance, product ratings, etc.

The SQL MAX function can be used on numeric columns to find the largest value, date and time columns to display the latest date. Apart from that, we can use the MAX function on string columns to display the row that starts with the highest alphabet (closer to Z).

SQL MAX Function syntax

The basic syntax of the MAX function to find the maximum value in a given column is as shown below.

SELECT MAX ([Column_Name])
FROM [Source]
WHERE condition

Apart from the above, we can use the SQL MAX function with subqueries, JOINS, GROUP BY, HAVING, PARTITION BY, etc. From the above syntax

  • Column_Name: It is the column on which you want to find the maximum value. The MAX function reads all rows in this column and returns the highest value.
  • Source: It is a single or multiple tables where the Column_Name is present.
  • Condition (optional): Please specify the condition to filter the records before applying the MAX() function.

NOTE: Generally, the MAX function ignores NULL values in the given column values while finding the maximum value. However, if there are no rows (not a single row) to select, it will return NULL as the output.

SQL MAX Function Examples

In this section, we will explain the working functionality of the MAX function with multiple examples. We start the section with a simple single-column example and cover the combination of the MAX function with WHERE, GROUP BY, HAVING clauses, SUBQUERY, JOINS, etc.

For the MAX aggregate function demonstration, we use the tables in the AdventureWorksDW database.

Finding the Maximum Value in a Column

As we mentioned earlier, the SQL Server MAX function returns the Maximum value from the total records present in the specified column. It is very common to find the maximum value in a column, such as the maximum list price, order quantity, sales amount, etc.

The following query will find the Maximum ListPrice present in the [ListPrice] column from the [DimProduct] table. If you observe the query, we used the ALIAS column name “HighestPrice” to display the highest list price value.

SELECT MAX(ListPrice) AS HighestPrice
FROM [DimProduct]

OUTPUT

HighestPrice
-------------
3578.27

Similarly, the query below returns the highest sales year to date of a person from the SalesPerson table.

SELECT MAX([SalesYTD]) AS HigestSales
FROM [Sales].[SalesPerson]
HigestSales
------------
4251368.5497

SQL MAX function on Multiple columns

We can also use the MAX function on multiple columns. In this example, we are finding the Maximum product cost and the Sales amount from the FactInternetSales table.

SELECT MAX(TotalProductCost) AS HighestCost,
    MAX(SalesAmount) AS HighestSale
FROM [FactInternetSales]
HighestCost           HighestSale
--------------------- ---------------------
2171.2942             3578.27

Finding the Latest Sales Date

One of the common real-time scenarios is to find the latest sales date that happened. It helps to track the sales activities. If you join this product name or customer information, it gives recent sales by a particular product, customer, postal code, etc. The query below uses the SQL MAX() function to return the latest sales date (order date) from the FactInternetSales table.

SELECT MAX(OrderDate) AS LastOrderedDate
FROM [FactInternetSales]
LastOrderedDate
-----------------------
2014-01-28 00:00:00.000

SQL MAX function with String data

We can also use the MAX function on a string or text column to find the highest text in a column. However, the result may not be the highest text value. For example, the query below returns the English product name (Women’s Tights, S) from the DimProduct table.

If you use the LEN() function, you can see “ML Mountain Frame-W – Silver, 40” is the highest character value. However, W is the maximum alphabet it finds in the EnglishProductName. If you sort the product name in descending order (Z -> A), “Women’s Tights, S” comes top, the highest one.

SELECT MAX(EnglishProductName) AS LongestProductName
FROM [DimProduct]
LongestProductName
-------------------------
Women's Tights, S

Similarly, the query below returns the product description with the highest alphabetical character.

SELECT MAX(EnglishDescription) AS LongestDescription
FROM [DimProduct]
LongestDescription
----------------------------
Wraps to fit front and rear tires, carrier and 2 keys included.

SQL MAX function with the WHERE clause

We can combine the MAX function with the WHERE clause to provide a condition (filter) before applying the aggregation. For example, finding the highest sales in the United States of America.

The query below returns the maximum list price for black color products. Here, the WHERE clause filters the products and selects only those with the Black color. Next, the MAX() function finds the maximum value in those black products.

SELECT MAX(ListPrice) AS HighestPrice
FROM [DimProduct]
WHERE Color = 'Black'
HighestPrice
---------------
3374.99

SQL MAX Function with ORDER BY Clause

You can use the MAX function with the ORDER BY Clause. The query below will find the highest YearlyIncome value of the customers grouped by Occupation. Next, the ORDER BY clause will sort those query results based on the Maximum YearlyIncome in ascending order. Within the ORDER BY clause, we can also use the ALIAS name, and it works fine.

SELECT EnglishOccupation
      ,MAX([YearlyIncome]) AS HighestIncome     
FROM [DimCustomer]
GROUP BY EnglishOccupation
ORDER BY MAX([YearlyIncome])
-- ORDER BY HighestIncome 
EnglishOccupation	HighestIncome
Manual	30000.00
Clerical	40000.00
Skilled Manual	90000.00
Professional	170000.00
Management	170000.00

SQL MAX function with GROUP BY Clause

We generally check the maximum product price (the highest priced product) belonging to a particular category or color, etc. In these situations, we use the GROUP BY Clause to group the products by color or category and then use the MAX function to find the highest-priced product present in each group.

The Aggregate Function query below groups products in the DimProduct table by color. The MAX function in the SELECT Statement finds and returns the maximum list price in each product color.

SELECT Color, MAX(ListPrice) AS HighestPrice
FROM [DimProduct]
GROUP BY Color
Color	HighestPrice
Black	3374.99
Blue	2384.07
Grey	125.00
Multi	89.99
NA	229.49
Red	3578.27
Silver	3399.99
Silver/Black	80.99
White	9.50
Yellow	2384.07

Similarly, the query below groups the sales information by order year. Next, the SQL MAX function displays the highest sales amount in each order year.

SELECT YEAR(OrderDate) AS OrderYear, MAX(SalesAmount) AS Sales
FROM [FactInternetSales]
GROUP BY YEAR(OrderDate)
OrderYear	Sales
2010	3578.27
2013	2443.35
2014	159.00
2011	3578.27
2012	2443.35

Apart from a single column, we can use multiple columns in the grouping and extract the highest value from them. The following query groups customers by Marital status and gender. Next, the MAX function finds the highest yearly income for each group (Male Single, Male Married, Female Single, and Female Married).

SELECT [MaritalStatus],[Gender]
      ,MAX([YearlyIncome]) AS HighestIncome     
FROM [DimCustomer]
GROUP BY [MaritalStatus],[Gender]
MaritalStatus	Gender	HighestIncome
M	M	170000.00
S	M	170000.00
S	F	170000.00
M	F	170000.00

SQL MAX function with HAVING clause

When we group the data, we sometimes check for the conditions against the aggregated data. In that circumstance, we use the HAVING Clause along with the GROUP BY Statement.

As you can see from the above result set, there are almost ten distinct color groups in the DimProduct table. However, the HAVING clause ensures that the maximum list price must be greater than 500 for each color group. It restricts the result set to displaying the List price less than 500.

SELECT Color, MAX(ListPrice) AS HighestPrice
FROM [DimProduct]
GROUP BY Color
HAVING MAX(ListPrice) > 500
Color	HighestPrice
Black	3374.99
Blue	2384.07
Red	3578.27
Silver	3399.99
Yellow	2384.07

The last line of the query checks whether each Group’s highest list price is higher than 500 or not. If this is true, the corresponding records will be displayed.

SQL MAX function with JOINS

So far, we have used the same table to find the maximum value for the entire table and the group. However, in real-time, we may have to use multiple tables using JOINs to find the maximum sales, orders, etc. The query below joins the product and the fact table. Next, it uses the product Color to group the table data and finds the maximum sales value in each color group.

SELECT Color, MAX(SalesAmount) AS HighestSales
FROM [DimProduct] p JOIN FactInternetSales f
ON p.ProductKey = f.ProductKey
GROUP BY Color
Color	HighestSales
Black	3374.99
Blue	2384.07
Multi	49.99
NA	159.00
Red	3578.27
Silver	3399.99
White	8.99
Yellow	2384.07

Using the SQL MAX function in a Subquery

Apart from the regular query, we can use the MAX function inside a subquery to filter the data. For example, the subquery in the following query finds the maximum product list price in the DimProduct table. The WHERE Clause checks whether the ListPrice of each product is greater than or equal to the highest list price (value coming from the subquery). If true, the main SELECT statement prints the columns.

SELECT ProductKey, EnglishProductName, Color,
	StandardCost, ListPrice
FROM DimProduct
WHERE ListPrice >= (SELECT MAX(ListPrice) FROM DimProduct)
ProductKey	EnglishProductName	Color	StandardCost	ListPrice
310	Road-150 Red, 62	Red	2171.2942	3578.27
311	Road-150 Red, 44	Red	2171.2942	3578.27
312	Road-150 Red, 48	Red	2171.2942	3578.27
313	Road-150 Red, 52	Red	2171.2942	3578.27
314	Road-150 Red, 56	Red	2171.2942	3578.27

From the first example, see that the Maximum List Price is 3578.27. So the above query will display the products whose List Price is greater than or equal to 3578.27.

Using the OVER Clause

Generally, the MAX function finds the largest value in a table or given group. However, we can use the OVER clause to make the MAX() function an analytical function. Within the OVER clause, we can specify the column to partition and another column to sort the details, and then apply the SQL MAX() function on that particular partition.

In the following query,

  • PARTITION BY YEAR(f.OrderDate) creates a partition for each year.
  • Within the year, MAX(SUM(f.SalesAmount)) find the highest value from the total sales.
  • Remember, MAX(f.SalesAmount) is the actual sales for the Year and product category group.
SELECT YEAR(f.OrderDate) AS SalesYear,
    pc.EnglishProductCategoryName AS ProductCategory,
    SUM(f.SalesAmount) AS TotalSales, MAX(f.SalesAmount) AS ActualSales,
    MAX(SUM(f.SalesAmount)) OVER (PARTITION BY YEAR(f.OrderDate)
    ) AS HighestSalesInYear
FROM FactInternetSales f
JOIN DimProduct p ON f.ProductKey = p.ProductKey
JOIN DimProductSubcategory ps 
    ON p.ProductSubcategoryKey = ps.ProductSubcategoryKey
JOIN DimProductCategory pc
    ON ps.ProductCategoryKey = pc.ProductCategoryKey
GROUP BY YEAR(f.OrderDate), pc.EnglishProductCategoryName
ORDER BY SalesYear, TotalSales DESC;
SalesYear	ProductCategory	TotalSales	ActualSales	HighestSalesInYear
2010	Bikes	43421.0364	3578.27	43421.0364
2011	Bikes	7075525.9291	3578.27	7075525.9291
2012	Bikes	5839695.3252	2443.35	5839695.3252
2012	Accessories	2147.08	159.00	5839695.3252
2012	Clothing	642.79	63.50	5839695.3252
2013	Bikes	15359502.36	2443.35	15359502.36
2013	Accessories	668241.53	159.00	15359502.36
2013	Clothing	323806.45	69.99	15359502.36
2014	Accessories	30371.35	159.00	30371.35
2014	Clothing	15323.37	69.99	30371.35
Categories SQL