Get highest marks scored by students in each school

Question

I have table STUDENT_MARKS:

SCHOOL_ID   STUDENT_ID      TTL_MARKS
--------------------------------------
8           24              78
8           25              76
9           33              88
9           34              85
9           35              88
10          41              68
10          42              68
10          43              68

My output should be:

SCHOOL_ID   STUDENT_ID      TTL_MARKS
--------------------------------------
8           24              78
9           33              88
9           35              88
10          41              68
10          42              68
10          43              68

I am trying with this query but not successful ::

SELECT 
    SCHOOL_ID, 
    STUDENT_ID, 
    MAX(TTL_MARKS) OVER(PARTITION BY SCHOOL_ID) 
FROM STUDENT_MARKS

How can I get the desired result in SQL Server? Please help!

Answer 1

You seem to want the original rows. So one method uses a subquery with window functions:

select sm.*
from (select sm.*, max(sm.ttl_marks) over (partition by school_id) as max_ttl_marks
      from student_marks sm
     ) sm
where ttl_marks = max_ttl_marks;

This query uses a window function to get the maximum marks for each school. The outer query then filters the rows so only the students with the maximum marks are in the result set.