The MAX function in SQL

The SQL MAX function is used to return the maximum value for the specified column in the SELECT statement.

In the case of a numeric column, the largest number is returned. For example, if a column contains 10, 20, 100, 1000 values then 1000 is returned as the maximum value.

If the specified column is character based then the highest value in the collating sequence is returned.

See the examples below for learning about the syntax and various uses of the MAX SQL function.

The example of MAX function for the numeric column

In this example, the highest age is returned from the employee’s table by using the MAX function. You may check the data for illustration from the full table and returned by MAX function as shown in the graphic below:

Query:

SQL SUM

The example of using character column in the SUM function

Check out the query below where I used the emp_name column in the MAX function for getting the highest value in the character column. See the employee name column and returned result:

SQL SUM character

You can see the name Shaun is returned by the MAX function. So, the MAX returns last value alphabetically for the character column.

The query using date column in MAX function

You may also use a date column in the MAX function for getting the highest date. For demonstrating that, I am using the same table as in the above example and using the “join_date” column. You may see the complete data in the first table and maximum date returned by using the MAX function in the table graphic below:

SQL Query:

SQL SUM DATE

The returned maximum date is 2017-04-03 which is the highest date if you look at the complete table data.

GROUP BY clause with MAX function example

This SQL query uses the MAX function with GROUP BY clause. For that, I am using another table “sto_emp_salary_paid” that stores employees salaries paid every month.

The query returns maximum salary paid to each employee.

Query:

SQL SUM GROUP BY

The first table shows complete data stored in the table. There, you can see different values for the salary for the same employee.

The SUM function with GROUP BY clause returned the employee name along with the maximum salary paid to that employee.

Adding the ORDER BY clause in above query

Just for the demo, I am adding the ORDER BY clause in above query to show how you may use the MAX function in the ORDER BY clause as well.

We will sort the results of employees with maximum salaries in descending order by using DESC keyword. That means the employees with the highest SUM will display at first and lowest at the last. Have a look at the query and result-sets:

SQL Query:

SQL SUM ORDER BY