Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to query the maximum value of mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

In mysql, you can use the "SELECT" statement and the MAX () function to query the maximum value, which returns the maximum value in a set of values, the syntax "SELECT MAX (DISTINCT expression) FROM data table name;".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In mysql, you can use the "SELECT" statement and the MAX () function to query the maximum value.

The MySQL MAX () function returns the maximum of a set of values. The MAX () function is very convenient in many queries, such as finding the maximum quantity, the most expensive product, and the customer's maximum payment.

The syntax of the MAX () function is as follows:

MAX (DISTINCT expression)

If you add the DISTINCT operator, the MAX function returns the maximum of different values, which is the same as the maximum of all values. This means that the DISTINCT operator has no effect on the MAX function (with or without the DISTINCT operator).

Note that the DISTINCT operator takes effect in other aggregate functions, such as COUNT,SUM and AVG.

Example of MySQL MAX function

Let's take a look at the payments table in the sample database (yiibaidb). The table structure is as follows-

Mysql > desc payments +-+ | Field | Type | Null | Key | Default | Extra | +-- -+ | customerNumber | int (11) | NO | PRI | NULL | checkNumber | varchar (50) | NO | PRI | NULL | | paymentDate | date | NO | | NULL | | amount | decimal (10L2) | NO | | NULL | +-- -+-+ 4 rows in set

To get the maximum payment in the payments table, you can use the following query:

SELECT MAX (amount) FROM payments

Execute the above query and get the following results-

Mysql > SELECT MAX (amount) FROM payments;+-+ | MAX (amount) | +-+ | 120166.58 | +-+ 1 row in setMySQL MAX function in the subquery

To get not only the maximum payment amount, but also other payment information, such as customer number, check number, and payment date, you can use the MAX function in the subquery, as shown below:

SELECT * FROM paymentsWHERE amount = (SELECT MAX (amount) FROM payments)

Execute the above query and get the following results-

+-+ | customerNumber | checkNumber | paymentDate | amount | +- -+ | 141 | JE105477 | 2015-03-18 | 120166.58 | +-+ 1 row in set

How does the above query run?

The subquery returns all the maximum payment amounts.

Other relevant payment information that the amount obtained by the external query is equal to the maximum amount returned from the subquery.

Another way not to use the MAX function is to use the ORDER BY clause to sort the result set in descending order, and use the LIMIT clause to get the first row, as shown in the query:

SELECT * FROM paymentsORDER BY amount DESCLIMIT 1

Execute the above query and get the following results-

+-+ | customerNumber | checkNumber | paymentDate | amount | +- -+ | 141 | JE105477 | 2015-03-18 | 120166.58 | +-+ 1 row in set

If you do not create an index on the amount column, the second query executes faster because it checks all rows in the payments table, while the first query checks all rows in the payments table twice, once in a subquery and once in an external query. However, if the amount column is indexed, the first query executes faster.

MySQL MAX with GROUP BY clause

To find the maximum value for each group, you can use the MAX function and the GROUP BY clause in the SELECT statement.

For each customer, the query obtains the maximum payment that the customer has paid, using the following query:

SELECT customerNumber, MAX (amount) FROM paymentsGROUP BY customerNumberORDER BY MAX (amount)

Execute the above query statement to get the following query results-

+-+-+ | customerNumber | MAX (amount) | +-+-+ | 219 | 4465.85 | 9658.74 | 381 | 14379.9 | 103 | 14571.44 | | 473 | 17746.26 | | 362 | 18473.71 | * A large wave of data is omitted here * | 105743 | 105743 | 111654.4 | 120166.58 | +-+ 98 rows in set MySQL MAX with HAVING clause |

You can use the MAX function in the GROUP BY clause in the HAVING clause to add filters for grouping based on specified criteria.

For example, the following query finds the maximum payment for each customer; and based on the amount returned, as in the following query, only looks for payments with an amount greater than 80000-

SELECT customerNumber, MAX (amount) FROM paymentsGROUP BY customerNumberHAVING MAX (amount) > 80000

Execute the above query and get the following results-

+-+-+ | customerNumber | MAX (amount) | +-+-+ | 82261.22 | 111654.4 | 120166.58 | 120166.58 | 105743 | | 85024.46 | 85024.46 | 239 | 80375.24 | | 321 | 85559.12 | +-+-+ 7 rows in set "how to query the maximum value for mysql" ends here Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.