SQL MAX function

What is SQL MAX function?

  • The SQL MAX function is used to return the maximum value for the specified column in the SELECT statement.
SELECT MAX(emp_age) As "Oldest Employee Age"
FROM sto_employees;
  • In the case of a numeric column, the largest number is returned. For example, if a column contains 10, 20, 100, and 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 to learn about the syntax and various uses of the MAX SQL function.

Syntax for using MAX function

SELECT MAX(column_name) FROM table_name WHERE condition;

In the syntax:

Parameters Description
column_name This specifies the column from where you want to get he maximum value.
table_name Provide the table name from where you want to retrieve data.
condition Optional. It specifies the condition for which the maximum value should be calculated.

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;

SQL SUM

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;

SQL SUM character

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;

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 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;

SQL SUM GROUP BY

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;

SQL SUM ORDER BY

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we solve the mysteries of coding together!