Search code examples
mysqlsubquerymax

How to get the two max values in table?

I have a table with different people that got different grades. I need to find the two persons with the highest grades. And if there is any ties, select them aswell. The only thing ive got right now is the 1st highest grade with this query:

SELECT name, 
       MAX(grade) AS max_grade 
FROM exercise_5 
GROUP BY name 
HAVING max_grade = ( SELECT MAX(grade) as max_grade 
                     FROM exercise_5 
                     GROUP BY name 
                     ORDER BY max_grade DESC LIMIT 1
                     );

Anyone know how its done?

Solution 
  • SELECT grades.*
FROM (
  SELECT name, MAX(grade) as max_grade 
  FROM exercise_5 
  GROUP BY 1
  ) grades
NATURAL JOIN (
  SELECT DISTINCT MAX(grade) as max_grade 
  FROM exercise_5 
  GROUP BY name
  ORDER BY max_grade DESC LIMIT 2
  ) max_grades
ORDER BY 2 DESC, 1 ASC;