Question 20452 in Mysql Question posted in
The official MySQL documentation can be found here.

MySql max is returning minimum value

enter image description here

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?

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    You could use INNER JOIN to join your data with the max phd_ratio :

    select t.*
from `rank` t 
inner join (
  select max(phd_ratio) as phd_ratio
  from `rank`
) as s on s.phd_ratio = t.phd_ratio;
    Login or Signup to reply.
  3. 0

    I suggest using one of the two

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

    OR

    SELECT * FROM rank ORDER BY phd_ratio DESC LIMIT 1

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search