How to implement Aggregate function properly?-CodePudding

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

CodePudding user response：

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