MySql max is returning minimum value

March 14, 2023

I have table rank and inside I have five different columns I want to fetch row record where I have maximun value for instance I have a column phd_ratio so I said:

SELECT * FROM rank HAVING max(phd_ratio)

Meaning I wanted to fetch row record where value was maximum but it returned me very first row where phd_ratio value was minimum so is there any way to do this?

>Solution :

You may either use a subquery to find the maximum phd_ratio:

SELECT *
FROM `rank`
WHERE phd_ratio = (SELECT MAX(phd_ratio) FROM `rank`);

Or use a LIMIT query:

SELECT *
FROM `rank`
ORDER BY phd_ratio DESC
LIMIT 1;

The second version is not suitable if two or more records could be tied for the highest phd_ratio, and you require all ties. In that case, use the first version. Also, as of MySQL 8+, RANK is a reserved keyword, and you should avoid naming your database tables and objects using it.