Imagine the following table:

result_id student subject grade
1         Anne    A      63
2         Anne    B      63
3         Bob     A      89
4         Bob     B      51
5         Carla   A      70
6         Carla   B      70
7         Carla   C      68
8         Dylan   B      75
9         Dylan   C      54

I would like to return the entries where a student got the exact same grade for subject A and B. So the ideal output would be:

result_id student subject grade
1         Anne    A      63
2         Anne    B      63
5         Carla   A      70
6         Carla   B      70

Can this even be achieved with queries? Struggling to find information about it.

CodePudding user response：

A bit complicated but I hope easy to read.

select * from the_table
where student in
(
 with t as 
 (
  select student, count(*) cnt, min(grade) mig, max(grade) mag 
  from the_table 
  where subject in ('A', 'B') group by student
 )
 select student from t where mig = mag and cnt = 2
) 
and subject in ('A', 'B') 
order by student, subject;