SQL Server MAX() Function

The MAX() function in SQL Server is a type of aggregate function. It is used to get the maximum or largest value in a group of values of a specified column or expression. It is the most commonly used built-in function in the SQL Server. This function accepts a single parameter that can be a column or valid expression and returns a single result to summarize the input data set. It can also work with the WHERE, GROUP BY, ORDER BY, and HAVING clauses to get the filtered result.

Syntax

The following syntax explain the MAX() function in SQL Server:

The function parameters are explained below:

aggregate_expression: It is the required expression that represents the column name from which the largest value is returned.

table_name(s): It indicates the name of a table from where we want to retrieve columns. There can be specified more than one table.

WHERE conditions: It is an optional clause to define a condition to filter the column or expressions.

DISTINCT: It returns the largest of the unique values in the expression. However, it does not affect the MAX() function and produces the same result without using this keyword.

NOTE: The MAX() function ignores NULL values. It returns NULL when no row is found.

MAX() Function Example

Let us understand the use of a MAX function in SQL Server with the help of various examples. Consider our database has a table named "ProductQty" that contains the following data:

Example1: This example uses the MAX() function to get the maximum quantity of the sold products in the ProductQty table:

This statement displays the following output where we see the maximum quantity of sold products:

Example2: This example uses the WHERE clause with the MAX() function. This clause is used to filter the records from the table. It is useful when we want to get the product name with the highest sold quantity. See the below statement:

Here, SQL Server first evaluates the subquery that uses the MAX() function and then the outer query display the product name whose quantity is equal to the result of the subquery. When we execute the statement, we will see the desired output:

Example3: This example uses the GROUP BY clause with the MAX() function. The GROUP BY clause collects data from multiple rows and groups them based on a specified column. For example, we can use the below statement to get the maximum quantity in all rows from the ProductQty table for each product group.

Executing the statement will display the maximum quantity of each product:

Example4: This example uses the HAVING clause with the MAX() function. We always use the HAVING clause with the GROUP BY clause to filter the grouped records. For example, if we want to get the maximum quantity of the sold product name greater than 300, the below statement can be used:

Executing the statement will return the below output:

Example5: This example uses the MAX() function with two columns to get the highest resultant output:

When we execute the statement, it will return the highest values of (Quantity * Price) for each product from the ProductQty table:

Example6: This example uses the DISTINCT and OVER() clause with the MAX() function. We use the DISTINCT clause to get the unique data, and the OVER clause determines which rows from the query are applied to the function, in what order the function evaluates them, and when the function's calculations should restart.

This statement returns the below output:

Example7: This example uses the MAX() function with the character data. Suppose we have considered the system database and want to get the database name that sorts the last name alphabetically. We can write the statement as below:

Here is the result:

Conclusion

This article will explain a complete overview of the MAX() function in SQL Server. Here we have learned how to get the highest values from the table with the help of WHERE, GROUP BY, HAVING, and OVER clauses.