Home >Database >Mysql Tutorial >The row that stores the maximum grouping value of a column in MySQL
Let us understand how to find the row holding the grouped maximum value of a specific column in MySQL -
Syntax for finding the row holding the grouped maximum value in MySQL The maximum value of a specific column is as follows-
SELECT colName1, colName2, colName3 FROM tableName s1 WHERE colName3=(SELECT MAX(s2. colName3) FROM tableName s2 WHERE s1. colName1= s2. colName1) ORDER BY colName1;
Suppose we have the following product table-
+---------+----------+--------+ | Article | Warehouse| Price | +---------+----------+--------+ | 1 | North | 255.50 | | 1 | North | 256.05 | | 2 | South | 90.50 | | 3 | East | 120.50 | | 3 | East | 123.10 | | 3 | East | 122.10 | +---------+----------+--------|
The following is the query−
SELECT Article, Warehouse, Price FROM Product p1 WHERE Price=(SELECT MAX(p2. Price) FROM Product p2 WHERE p1. Article= p2. Article) ORDER BY Article;
+-------------+----------------+------------+ | Article | Warehouse | Price | +-------------+----------------+------------+ | 0001 | North | 256.05 | | 0002 | South | 90.50 | | 0003 | East | 123.10 | +-------------+----------------+------------+
The above query uses a correlated subquery.
The above is the detailed content of The row that stores the maximum grouping value of a column in MySQL. For more information, please follow other related articles on the PHP Chinese website!