CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Ekaku509
Ekaku509

Reputation: 17

How do I grab each student’s 3rd max assignment mark in each subject

I am trying to write an sql that will allow me select each student’s 3rd best assignment mark in each subject. I have tried with the query below but it isn't working for me. I will be grateful to get some answers. I am getting an error [Code: 0, SQL State: 21000] ERROR: more than one row returned by a subquery used as an expression. This is the table structure Students , Courses(Id) , bridging table called StudentsCourses(ID, StudentID,CourseID) and then assignment table which has StudentsCourse(FK) and Grade

select max(Assignments.Grade)
from Assignments
where grade < (select max(Assignments.Grade)
         from Assignments
         where grade  <  (select max(Assignments.Grade)
                         from Assignments
                         group by Assignments.StudentCourseID))

Upvotes: 1

Views: 51

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Use row_number():

select a.*
from (select a.*,
             row_number() over (partition by student_id, StudentCourseID order by grade desc) as seqnum
      from assignments a
     ) a
where seqnum = 3;

Note: If all the assignments have the same value, this will return the highest value.

If you want the third highest distinct score, then use dense_rank() instead of row_number().

Upvotes: 0

GMB
GMB

Reputation: 222462

You can use window functions:

select *
from (
    select a.*, row_number() over(partition by student_id, subject_id order by grade desc)
    from assignments a
) a
where rn = 3

Your question is a bit unclear about the structure of table assignments. This assumes that a student is identified by student_id and a subject by subject_id - you many need to ajust that to your actual column names.

Upvotes: 1

Related Questions