How to get the two max values in table?-CodePudding

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?

CodePudding user response：

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;

CodePudding user response：

Use this Code(This code fails to show ties)

with CTE AS (SELECT *,ROW_NUMBER() OVER(ORDER BY grade DESC) AS rn FROM Notes) SELECT name, grade FROM CTE WHERE rn IN (1,2);

Use this one to show ties:

with CTE AS (SELECT *,RANK() OVER(ORDER BY grade DESC) AS rn FROM Notes) SELECT name, grade FROM CTE WHERE rn IN (1,2);