The Row Holding the Maximum of a Certain Column in MySQL

Let us understand how to find the row that holds the maximum of a specific column in MySQL −

Note: We assume we have created a database named ‘DBNAME’ and a table named ‘tableName’.

Let us see how to fetch the row that holds the maximum value of a specific column using a MySQL query −

This can be done using the subquery. Here, we are fetching the maximum value of colName3 −

Query

SELECT colName1, colName2, colName3
FROM tableName
WHERE colName3=(SELECT MAX(colName3) FROM tableName);

Output

+--------------+--------------+--------------+
| colName1     | colName2     | colName3     |
+--------------+--------------+--------------+
| 0003         | D            | 20.05        |
+--------------+--------------+--------------+

Other solutions include using a LEFT JOIN or sorting all the rows in descending order by the specific column. This would give the first row with the LIMIT clause (it is specific to MySQL).

SELECT colName1, colName2, colName3
FROM tableName
WHERE colName3=(SELECT MAX(colName3) FROM tableName);
SELECT s1.colName1, s1. colName2, s1. colName3
FROM tableName s1
LEFT JOIN tableName s2 ON s1. colName3< s2. colName3
WHERE s2.colName1 IS NULL;

SELECT colName1, colName2, colName3
FROM tableName
ORDER BY colName3 DESC
LIMIT 1;

If there are multiple maximum values in a column each of which have the same value, then the LIMIT clause would only show one of the column values.

AmitDiwan

Updated on: 08-Mar-2021

93 Views

Kickstart Your Career

Get certified by completing the course

Get Started