Second highest grade for each student

Question

ID_S    STUDENT_ID  GRADE
1   1   22
2   1   33
3   1   44
4   1   55
5   2   66
6   2   22
7   2   33
8   2   44
9   3   55
10  3   66
11  3   22
12  3   33
13  4   44
14  4   55
15  4   66
16  4   22
17  5   33
18  5   44
19  5   55
20  5   66

I have to extract second highest grades for 5 students (each having four different grades). The table should look like this:

Student_id  Grades
1             44
2             44
3             55
4             55
5             55

but instead, the grade for the first student comes as 55.

this is my code:

select student_id, max(grade) as "second highest grades" from grade_list7 
where grade not in (select max(grade) from grade_list7)
group by student_id;

What exactly is wrong here?

Answer 1

You need something like the RANK() analytic function here:

WITH cte AS (
    SELECT g.*, RANK() OVER (PARTITION BY student_id ORDER BY grade DESC) rnk
    FROM grade_list7 g
)

SELECT student_id, grade
FROM cte
WHERE rnk = 2
ORDER BY student_id;

The issue of two or more grades being tied may be important here, and you might want to use DENSE_RANK() instead of RANK(), depending on the behavior you want.