SQL MAX function

SELECT MAX(emp_age) As "Oldest Employee Age"
FROM sto_employees;

Syntax for using MAX function

In the syntax:

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:

SELECT MAX(emp_age) As "Oldest Employee Age"

FROM sto_employees;

The example of using a character column in the SUM function

Check out the query below where I used the emp_name column in the MAX function to get the highest value in the character column.

See the employee name column and returned result:

SELECT MAX(emp_name) As "Max Employee Name"

FROM sto_employees;

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

The query using the date column in MAX function

You may also use a date column in the MAX function to get the highest date. To demonstrate 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 the maximum date returned by using the MAX function in the table graphic below:

SQL Query:

SELECT MAX(join_date) As "Maximum Date"

FROM sto_employees;

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 the GROUP BY clause. For that, I am using another table “sto_emp_salary_paid” that stores employees’ salaries paid every month.

The query returns the maximum salary paid to each employee.

Query:

SELECT emp_name, MAX(emp_sal_paid) As "Maximum Salary"

FROM sto_emp_salary_paid

GROUP by emp_name;

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

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

Adding the ORDER BY clause in the above query

Just for the demo, I am adding the ORDER BY clause in the 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 set:

SQL Query:

SELECT emp_name, MAX(emp_sal_paid) As "Maximum Salary"

FROM sto_emp_salary_paid

GROUP BY emp_name

ORDER BY MAX(emp_sal_paid) DESC;