Second highest grade for each student-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

Your subselect needs to get the max for the specidifc user, so you need to add the stundet id

Or you can use the RANK approach or Maybe DENSE_RANK

select student_id, max(grade) as "second highest grades" from grade_list7 g7
where grade not in (select max(grade) from grade_list7 WHERE student_id = g7.student_id)
group by student_id
ORDER BY student_id;

STUDENT_ID | second highest grades
---------: | --------------------:
         1 |                    44
         2 |                    44
         3 |                    55
         4 |                    55
         5 |                    55

WITH CTE AS (
SELECT 
student_id,grade, RANK() OVER( PARTITION BY student_id ORDER BY grade DESC) rn
FROM grade_list7)
SELECT 
student_id,grade FROM CTE WHERE rn = 2

STUDENT_ID | GRADE
---------: | ----:
         1 |    44
         2 |    44
         3 |    55
         4 |    55
         5 |    55

db<>fiddle here

CodePudding user response：

Perhaps this may work:

SELECT student_id, MAX(grade) AS "Second Highest Grades"
FROM grade_list7
WHERE grade < (SELECT MAX(grade) FROM grade_list7)
GROUP BY student_id;