Selecting Names with Highest Aggregate in each Class

Question

I'm new to SQL and I'm working with the following tables:

CREATE TABLE Student (
  StudentID int,
  Name nvarchar(30),
  Class nvarchar(30)
);

INSERT INTO Student (StudentID, Name, Class) 
VALUES (1,'Alfredo','X'), (2,'Jack','X'), (3,'Chris','Y'), (4,'Paul','Y');

CREATE TABLE Subject (
  SubjectID int,
  Name nvarchar(30),
  Class nvarchar(30)
);
INSERT INTO Subject (SubjectID, Name, Class)
VALUES (1,'Maths','X'), (2, 'Science','X'), (3, 'English','Y'), (4, 'Arts','Y');


CREATE TABLE Performance (
  StudentID int,
  SubjectID int,
  Marks int
);

INSERT INTO Performance (StudentID, SubjectID, Marks)
VALUES 
(1,1,61),(1,2,75),
(2,1,82),(2,2,64),
(3,3,82),(3,4,83),
(4,3,77),(4,4,81);

I'm trying to write a query to retrieve student names with the highest aggregate marks in each class.

My Expected Output is:

StudentID   Name    Marks   Class
2   Jack    146 X
3   Chris   165 Y

I tried using the following query to merge tables

SELECT * FROM Performance AS p

INNER JOIN
Student AS s
ON p.StudentId = s.StudentId

and this query to retrieve Aggregate Marks

SELECT StudentID, SUM(MARKS) FROM Performance GROUP BY StudentID

I'm quite clueless on how to merge these two queries to achieve my objective. Are there any other workarounds?

Please Advise

Answer 1

here is how you can do it :

select * from 
(
select s.StudentID, name, class
  , sum(marks) Marks,row_number() over (partition by class order by sum(marks) desc) rn 
from Performance p
join Student s
 on s.StudentID = p.StudentID
group by StudentID ,name,class
) t
where rn = 1

db<>fiddle here