Selecting Names with Highest Aggregate in each Class

April 07, 2021, at 3:30 PM

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

  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');
  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)

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
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