Max function returns the maximum value in the expression. MAX can be used with numeric, character, and datetime columns, but not with bit columns. MAX ignores any null values. For character columns, MAX finds the highest value in the collating sequence. 

Syntax of Max function :

MAX ( [ ALL | DISTINCT ] expression )

ALL applies the aggregate function to all values. ALL is the default.

DISTINCT means each unique value is considered.

Expression is a constant, column name, or function, and any combination of arithmetic, bitwise, and string operators. Aggregate functions and subqueries are not permitted.

It Returns a value same as expression.

Note : MAX(DISTINCT column_name) is not supported when using CUBE or ROLLUP.

Example of  MAX Function :

Example 1 : Using max function in select query.

SELECT  MAX(Subtotal) AS MaximumOrder

FROM     Order_Subtotals

Output

MaximumOrder

16387.50

Above example returns highest order amount from Order_Subtotals table.

Example 2 : Using max function with subquery in a where clause 

SELECT  OrderID, Subtotal

FROM    Order_Subtotals

WHERE   Subtotal  >  (SELECT MAX(SaleAmount)

                                     FROM Sales_Totals_by_Amount)

ORDER BY Subtotal DESC;

Output

OrderID      Subtotal

10865         16387.50

10981         15810.00

11030         12615.05

10889         11380.00

Above query instructs the outer query to select records whose Subtotal is greater than highest value of SaleAmount from Sales_Totals_by_Amount. It means it will reaturn records whose value is greater than the greatest value returned by the subquery. Use of Max function ensures that only a single value would be returned for comparison purpose.

You can achieve same result using ALL logical operator in the subquery. To view this example,please visit below link.

All Operator