MAX (Transact-SQL)

Syntax

MAX ( [ALL | DISTINCT] expression ) [ OVER ( [ partition_by_clause ] order_by_clause ) ]

Parameters

ALL : Applies the aggregate function to all values. ALL is the default.
DISTINCT : Specifies that DISTINCT is applied to the aggregate function. Returns the maximum of the unique values in the expression.
expression : An expression that returns a value of any data type that is comparable. This can be a column name or an expression that is composed of column names with operators, constants, and other functions.
OVER ( [ partition_by_clause ] order_by_clause ) : Specifies that the aggregate function is to be applied to a set of rows that define a partition of the result set. If partition_by_clause is specified, the function is applied to each partition independently. If order_by_clause is specified, it determines the order in which the operations are performed.

Return Value

Returns the maximum value from the specified expression. The data type of the return value is the same as the data type of the expression.

Description

The MAX function is an aggregate function. It operates on a set of rows and returns a single value. It can be used in a SELECT statement to return the largest value of a specified column or expression across a group of rows.

When used with the OVER clause, MAX becomes a window function. This allows you to calculate the maximum value within a specified window (partition) of rows without collapsing the rows.

Examples

Example 1: Finding the highest salary in an employee table

SELECT MAX(Salary) AS HighestSalary
FROM Employees;

This query will return a single row with the highest salary from the Employees table.

Example 2: Finding the latest order date

SELECT MAX(OrderDate) AS LatestOrderDate
FROM Orders;

This query returns the most recent date from the OrderDate column in the Orders table.

Example 3: Using MAX with DISTINCT

SELECT MAX(DISTINCT UnitPrice) AS MaxUniquePrice
FROM Products;

This query finds the highest unique price among all products.

Example 4: Using MAX as a Window Function

SELECT
    EmployeeName,
    Salary,
    MAX(Salary) OVER (PARTITION BY Department) AS MaxSalaryInDepartment
FROM Employees;

This query returns each employee's name and salary, along with the maximum salary within their respective department. The original rows are preserved.

Considerations

MAX ignores NULL values.
If the expression evaluates to no rows, MAX returns NULL.
The data type of the return value depends on the data type of the input expression.