The MAX function in SQL Server is used to return the maximum value in a set of values in a specific column of a table. It can be used in a SELECT statement to retrieve the highest value in a column, or it can be used in a WHERE clause to filter the results based on a maximum value.

MAX syntax

SELECT MAX(column_name)
FROM table_name

MAX example

For example, the following query returns the highest salary from the "employees" table:

SELECT MAX(salary) FROM employees;

You can also use MAX function with GROUP BY clause to get the max value of each group.

SELECT department, MAX(salary)
FROM employees
GROUP BY department;

Keep in mind that the column on which the MAX function is applied must be of numeric data type, either an integer or floating-point number.

Sales table:

The following query returns the maximum price from the store table.

SELECT MAX(price) FROM store;

Result: 900