How to query the maximum value in mysql

In mysql, you can use the "SELECT" statement and the MAX() function to query the maximum value. This function can return 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.

MySQLMAX()The function returns the maximum value in a set of values. TheMAX()function is very convenient in many queries, such as finding the maximum quantity, the most expensive product, and the maximum payment by a customer.

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

MAX(DISTINCT expression);

If you add theDISTINCToperator, theMAXfunction returns a different The maximum value of a value, which is the same as the maximum value of all values. This means that theDISTINCToperator will not have any impact on theMAXfunction (it does not matter whether theDISTINCToperator is used or not).

Please note that theDISTINCToperator takes effect within otheraggregation functionssuch as COUNT, SUM, and AVG.

MySQL MAX function example

Let’s take a look at thepaymentstable in the sample database (yiibaidb). Its 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(10,2) | NO | | NULL | | +----------------+---------------+------+-----+---------+-------+ 4 rows in set

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

SELECT MAX(amount) FROM payments;

Execute the above query statement and get the following results-

mysql> SELECT MAX(amount) FROM payments; +-------------+ | MAX(amount) | +-------------+ | 120166.58 | +-------------+ 1 row in set

MySQL MAX function in 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 do it in the subquery Use theMAXfunction as follows:

SELECT * FROM payments WHERE amount = ( SELECT MAX(amount) FROM payments);

Execute the above query statement 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 statement run?

• Subquery returns all maximum payment amounts.
• The outer query retrieves other related payment information whose amount is equal to the maximum amount returned from the subquery.

An alternative to not using theMAXfunction is to use theORDER BYclause to sort the result set in descending order and useLIMITclause gets the first row, query as follows:

SELECT * FROM payments ORDER BY amount DESC LIMIT 1;

Execute the above query statement and get the following results-

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

If you have not created on theamountcolumn index, the second query executes faster because it checks all rows in thepaymentstable, whereas the first query checks all rows in thepaymentstable twice , once in the subquery and once in the outer query. However, if theamountcolumn is indexed, the first query will execute faster.

MySQL MAX with GROUP BY clause

To find the maximum value for each group, you can do it inSELECTUseMAXfunction andGROUP BYclause in the statement.

For each customer, query to obtain the maximum payment the customer has paid, you can use the following query:

SELECT customerNumber, MAX(amount) FROM payments GROUP BY customerNumber ORDER BY MAX(amount);

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

+----------------+-------------+ | customerNumber | MAX(amount) | +----------------+-------------+ | 219 | 4465.85 | | 198 | 9658.74 | | 381 | 14379.9 | | 103 | 14571.44 | | 473 | 17746.26 | | 362 | 18473.71 | ******* 此处省略一大波数据 ****** | 148 | 105743 | | 124 | 111654.4 | | 141 | 120166.58 | +----------------+-------------+ 98 rows in set

MySQL MAX with HAVING clause

You can useMAX## in theHAVINGclause #Function to add filters to groups based on specified conditions.For example, the following query finds the maximum payment for each customer; and based on the returned amount, such as the following query statement, only queries for payments exceeding80000

-

SELECT customerNumber, MAX(amount) FROM payments GROUP BY customerNumber HAVING MAX(amount) > 80000;

is executed The above query statement yields the following results -

+----------------+-------------+ | customerNumber | MAX(amount) | +----------------+-------------+ | 114 | 82261.22 | | 124 | 111654.4 | | 141 | 120166.58 | | 148 | 105743 | | 167 | 85024.46 | | 239 | 80375.24 | | 321 | 85559.12 | +----------------+-------------+ 7 rows in set

[Related recommendations:

mysql video tutorial

]

The above is the detailed content of How to query the maximum value in mysql. For more information, please follow other related articles on the PHP Chinese website!