1

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))
Ekaku509
  • 17
  • 6

2 Answers2

1

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • 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 – Ekaku509 Apr 08 '20 at 01:21
  • @Ekaku509: please don't put additional information into comments. [**edit**](https://stackoverflow.com/posts/61091250/edit) instead –  Apr 08 '20 at 04:58
0

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().

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 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. Could you explain the row_number and use of dense_rank – Ekaku509 Apr 08 '20 at 01:22
  • @Ekaku509 . . . Your comment is nice, but your question only references `assignments`, so that is what this answer addresses. You can read the documentation for Postgres to better understand Postgres functions. – Gordon Linoff Apr 08 '20 at 01:25
  • Yeah, I am actually looking at how I can use the Student Name, and the course Name ad well from the tables I mentioned. How do I do that together with the assignment table? table inclusiveThanks so much. I may have another question about another so i will get some answers about that query – Ekaku509 Apr 08 '20 at 02:26
  • **Will this be able to find me the : the average final mark in each class (assuming only top four assignments per student were counted, if student has less than four assignments assume a score of zero for the remainder ? ** `select * from ( select s.*, a.grade, row_number() over(partition by s.studentID,s.courseID order by grade desc) as rn from assignments a, StudentCourses s where a.StudentCourseID = s.StudentCourseID ) a where rn < 5]` – Ekaku509 Apr 08 '20 at 03:11