SQL query to get the subject with the max score

Question

name    subject classroom   Total_Score  term   session 
jane     eng     phase1      79          1      2016/2017
jane     math    phase1      56          1      2016/2017
jane     eng     phase1      98          2      2016/2017
jane     math    phase1      87          2      2016/2017
jack     eng     phase1      94          1      2016/2017
jack     math    phase1      45          1      2016/2017
jack     eng     phase1      79          2      2016/2017
jack     math    phase1      89          2      2016/2017
jane     eng     phase2      55          1      2017/2018
jane     math    phase2      37          1      2017/2018
jack     math    phase2      45          1      2017/2018
jack     eng     phase2      59          2      2017/2018

Hi, I have this table and I am trying to get the subject a student got the max score in the term and session. The expected result is supposed to look like the table below

name   subject   classroom  max_score  term     session
jane     eng     phase1      79          1      2016/2017
jane     eng     phase1      98          2      2016/2017
jack     eng     phase1      94          1      2016/2017
jack     math    phase1      89          2      2016/2017
jane     eng     phase2      55          1      2017/2018
jack     eng     phase2      59          2      2017/2018

I tried the following query

SELECT
  distinct name, subject, classroom, max(Total_Score), term, session
FROM
  ranktable
 group by name, classroom, term, session, subject
 order by term

but it does not return the desired output. I will appreciate any help I can get. I am relatively new to SQL

Answer 1

A cross database solution is to filter with a correlated subquery:

select t.*
from mytable t
where t.total_score = (
    select max(t1.total_score)
    from mytable t1
    where 
        t1.name = t.name 
        and t1.term = t.term 
        and t1.session = t.session
)

This gives you the row with top score per name, term and session.