MAX Function : SQL SERVER

The MAX function returns the maximum value of an expression.
The syntax for the MAX function is:

SELECT MAX(expression )
FROM tables
WHERE predicates;


Simple Example
For example, you might wish to know the maximum salary of all employees.

SELECT MAX(salary) as "Highest salary"
FROM employees;

In this example, we've aliased the max(salary) field as "Highest salary". As a result, "Highest salary" will display as the field name when the result set is returned.
Example using GROUP BY

In some cases, you will be required to use a GROUP BY clause with the MAX function.
For example, you could also use the MAX function to return the name of each department and the maximum salary in the department.

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

Because you have listed one column in your SELECT statement that is not encapsulated in the MAX function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.