MAX() – Find the Maximum Value in a Column in MySQL
The MySQL MAX() function is an aggregate function that returns the maximum value from an expression.
Typically, the expression would be a range of values returned as separate rows in a column, and you can use this function to find the maximum value from the returned rows. If there are no matching rows, MAX() returns NULL .
For example, you can use this function to find out which city has the largest population out of a list of cities.
Syntax
The syntax of MAX() goes like this:
Where expr is the expression for which you want the maximum value.
The over_clause is an optional clause that works with window functions. Note that the over_clause can only be used if you don’t use the DISTINCT keyword.
The (optional) DISTINCT keyword can be used to eliminate duplicate values.
Basic Example
First, here’s the raw data that we’ll use in this example:
We can use the MAX() function to find the city with the largest population (i.e. the row with the maximum value in its population column).
The GROUP BY Clause
We can use the GROUP BY clause to list out each country, along with the population of that country’s largest city (by population):
The ORDER BY Clause
We can also use the ORDER BY clause to specify a column with which to order by:
This orders the results in ascending order, which lists the minimum value first.
Note that, when ordering by a multi-word alias (like `Max Value` ), you need to use the backtick character ( ` ) instead of the apostrophe ( ‘ ) to surround the two words.
Find the Maximum Character Length
The MAX() function isn’t limited to just columns with numerical data. You can also combine MAX() with other functions to return maximum values in other areas.
For example, using our sample data, we can find the value with the maximum number of characters in the City column:
We can also see this by using the following query (which doesn’t involve the MAX() function):
Using an OVER Clause
As mentioned, the syntax allows for an OVER clause to be included in your queries. Basically, the OVER clause allows you to specify how to partition query rows into groups for processing by the window function.
Here’s an example:
This example partitions the rows by District , providing the maximum value for each partition (district). This allows you to see more granular data, such as each city’s population, along with the population of the largest city in the same district.
MySQL MAX
Summary: in this tutorial, you will learn how to use the MySQL MAX() function to get the maximum value in a set of values.
Introduction to MySQL MAX() function
The MySQL MAX() function returns the maximum value in a set of values. The MAX() function comes in handy in many cases such as finding the greatest number, the most expensive product, and the largest payment from customers.
Here is the basic syntax of the MAX() function :
If you add the DISTINCT operator, the MAX() function returns the maximum value of distinct values, which is the same as the maximum value of all values. It means that DISTINCT does not take any effects in the MAX() function.
Notice that DISTINCT has effects in other aggregate functions such as COUNT() , SUM() , and AVG() .
MySQL MAX() function examples
We’ll use the payments table in the sample database to demonstration the MAX() function.
A) Using MySQL MAX() function to find the maximum value in a column example
This example uses the MAX() function to return the largest amount of all payments:
In this example, the MAX() function checks all values in the amount column of the payments table to find the biggest amount.
B) Using MySQL MAX() function with WHERE clause
This statement uses the MAX() function to find the largest payment in 2004:
In this example:
- First, use a condition in the WHERE clause to get only payments whose year is 2004. We used the YEAR() function to extract year from the payment date.
- Then, use the MAX() function in the SELECT clause to find the largest amount of payments in 2004.
This picture shows the output:
C) Using MySQL MAX() function in subquery example
To get not only the largest payment’s amount but also other payment’s information such as customer number, check number, and payment date, you use the MAX() function in a subquery as shown in the following query:
- The subquery returns the largest amount of all payments.
- The outer query gets the payment whose amount is equal to the largest amount returned from the subquery and also other payment’s information.
Another way to do this without using the MAX() function is to sort the result set in descending order using the ORDER BY clause and get the first row in the result set using the LIMIT clause as follows:
If you don’t have an index on the amount column, the second query with the LIMIT clause is faster because it examines all rows in the payments table, while the first query examines all the rows in the payments table twice, first once in the subquery and another in the outer query.
However, if the amount column is indexed, the first query executes faster.
D) Using MySQL MAX() with GROUP BY clause example
To find the maximum value for every group, you use the MAX function with the GROUP BY clause.
This statement uses the MAX() to get the largest payment of each customer:
In this example:
- First, the GROUP BY clause group payments into groups by customer number.
- Second, the MAX() function returns the largest payment in each group.
E) Using MySQL MAX() with HAVING clause
When you use the MAX() function with the GROUP BY clause, you can find the maximum value for each group.
If you want to filter groups based on a condition, you can use the MAX() function in a HAVING clause.
The following query finds the largest payment of each customer; and based on the returned payments, get only payments whose amounts are greater than 80,000 .
If you want to see the names of customers instead of numbers, you can join the payments table with the customers table:
Here is the output:
In this tutorial, you have learned how to use the MySQL MAX() function to find the maximum value in a set of values.
How do I find the largest value in a column in postgres sql?
How do I only return the name of the person with the largest weight?
4 Answers 4
Much more performant than the other answer and results in one row only.
If you need to find multiple rows, e.g. date on which each person had maximum weight:
i.e. for «don» you want to get «don | 120 | 20» and for joe you want «joe | 90 | 1» , then you can write:
ORDER BY DESC puts rows with null values at the top.
To avoid returning results corresponding to null values:
SELECT name FROM tbl WHERE weight = (SELECT MAX(weight) FROM tbl);
Note: This query will return multiple results if multiple people have a weight equal to the maximum weight. To grab just one, add LIMIT 1 to the end of the query.
SQL MAX
Summary: in this tutorial, you will learn how to find the maximum value in a group by using the SQL SUM function.
Introduction to SQL MAX function
SQL provides the MAX function that allows you to find the maximum value in a set of values. The following illustrates the syntax of the MAX function.
The MAX function ignores NULL values.
Unlike the SUM, COUNT, and AVG functions, the DISTINCT option is not applicable to the MAX function.
SQL MAX function examples
We will use the employees table to demonstrate how the MAX function works.
The following SELECT statement returns the highest (maximum) salary of employees in the employees table.
To get the employees who have the highest salary, you use a subquery as follows:
The subquery returns the highest salary. The outer query gets the employees who have the salary that equals the highest salary.
SQL MAX with GROUP BY example
We usually use the MAX function in conjunction the GROUP BY clause to find the maximum value per group.
For example, we can use the MAX function to find the highest salary of employee in each department as follows:
To include the department names in the result, we join the employees table with the departments table as follows:
SQL MAX with ORDER BY example
Like other aggregate functions, to sort the result set based on the result of the MAX function, we have to place the MAX function in the ORDER BY clause.
For example, the following statement returns the highest salaries of employees in each department and sorts the result set based on the highest salaries.
SQL MAX with HAVING example
We use the MAX function in the HAVING clause to add the condition to the groups that summarized by the GROUP BY clause.
For example, to get the department that has employee whose highest salary is greater than 12000, you use the MAX function in the HAVING clause as follows:
In this tutorial, we have shown you how to use the MAX function to find the maximum value in a group of values.