How to implement Aggregate function properly?

Question

I have two tables in my project. Students table and Lessons table. They are connected through a foreign key (Id in Students table and StudentId in Lessons table). I want to return the highest grade for each lesson with the name of the student. I know I should implement aggregate function. It works fine for returning the highest grade of all the lessons, but I have no idea how to return the highest grade for a specific lesson (Math for example).

select s.Name, l.Grade
from Students s
inner join Lessons l on s.Id = l.StudentId
where l.Grade = (select MAX(Grade) from Lessons)

public class StudentClass
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public List<Lesson> Lessons { get; set; }
    }

public class Lesson
        public int Id { get; set; }
        public string Name { get; set; }
        public int Grade { get; set; }
        public int StudentId { get; set; }
        [ForeignKey("StudentId")]
        public StudentClass Student { get; set; }
    }

Can you provide scripts for creating tables and filling it with sample data. Also provide expected output please — Peter
– Peter, Commented Jan 27, 2023 at 8:39
Highest grade only for a lesson? Don't you want the lesson name (or similar) too, so you know which lesson it is? — jarlh
– jarlh, Commented Jan 27, 2023 at 8:41
If you want the highest grade per lesson per student, you can use select s.Name, l.Name as Lesson, MAX(l.Grade) from ... GROUP BY s.Name,l.Name. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 27, 2023 at 8:43
It's a .net core project with a code-first approach. They are just two tables. Students => (Id, Name), Lessons => (Id, Name, Grade, StudentId). I expect to return the highest grade in math with the name of the student. — amin khosravi
– amin khosravi, Commented Jan 27, 2023 at 8:43
Your sql ain't far off. You can change select MAX(Grade) from Lessons => select MAX(Grade) from Lessons l2 WHERE l2.Name = l.Name. This will only get max grade for the actual subject name. — siggemannen
– siggemannen, Commented Jan 27, 2023 at 10:02

siggemannen · Accepted Answer · 2023-01-27 13:16:59Z

1

A bit more modern solution is to use ROW_NUMBER:

select *
from (
  select s.Name, l.Grade, l.Name
  , ROW_NUMBER() OVER(PARTITION BY l.Name ORDER BY Grade DESC) AS sort
  from Students s
  inner join Lessons l on s.Id = l.StudentId
  ) les
WHERE les.sort = 1

This creates a counter per each less name ordered by grade descending and then you retrieve the first result

answered Jan 27, 2023 at 13:16

siggemannen

10.1k3 gold badges12 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ben Thul Over a year ago

One note for the OP - if it's possible for more than one student to have the top grade, replacing ROW_NUMBER() with RANK() will return all such students.

Collectives™ on Stack Overflow

How to implement Aggregate function properly?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related