Query the second highest value of a name, if there's only one value return null

user_15875800 0
Viewed 67

I have a table which has 2 columns, named "Name" and "Score", demonstrates the academic result of a class.

"Name": Andy, Amy, Chloe, John, etc.

"Score": From 0 to 100

Some of the students had submitted all the tests but some have not done yet due to pandemic, in other words, there's some student only got only 1 result. Now I need to figure out what is the second-highest score that each of them has made.

If the students only submitted once, means that they don't have the second-highest score so we will return null for the score.

I tried

SELECT 
name,
    CASE
        WHEN COUNT(score) IS NULL THEN NULL
        ELSE MAX(score)
    END AS second_highest
FROM
    result
WHERE
    score NOT IN (SELECT 
            MAX(score)
        FROM
            result
        GROUP BY name)
GROUP BY name
ORDER BY name;

But seems like the subquery excluded the only score of the one-submission students, so they don't show up on the query result. Is there anything I need to change in this method or is there any better approach?

Input and output can be found here sorry I am not familiar with demonstrating sample on Stackoverflow I use MySQL version 8.0.22

mysql sql
1 Answers

Use ROW_NUMBER() window function to rank the scores for each student and then get the 2nd highest score with conditional aggregation:

SELECT name,
       MAX(CASE WHEN rn = 2 THEN score END) second_highest
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY score DESC) rn
  FROM result
) t
GROUP BY name

See the demo.

user_104988280
Related