Student with greater average grades by major

Question

Tables and data:

CREATE TABLE major
( 
    id INT PRIMARY KEY, 
    name VARCHAR(200)
);

insert into major values 
    (101, 'Computing'), 
    (102, 'Arquitecture');

CREATE TABLE student
( 
    id INT PRIMARY KEY, 
    name VARCHAR(200), 
    major_id INT,
    foreign key(major_id) references major(id)
);

insert into student  values
    (1001, 'Claude', 101),
    (1002, 'John', 101),
    (1003, 'Peter', 102);

CREATE TABLE course
( 
    id INT PRIMARY KEY, 
    name VARCHAR(200)
);

insert into course values 
    (901, 'Databases'), 
    (902, 'Java'),
    (903, 'Artificial Intelligence'), 
    (904, 'OOP');
    
CREATE TABLE grades
(
   student_id INT,
   course_id INT,
   grade integer,
   primary key (student_id, course_id),
   foreign key(student_id) references student(id),
   foreign key(course_id) references course(id)
);

insert into grades  values
    (1001, 903, 95),
    (1001, 904, 88),
    (1002, 901, 76),
    (1002, 903, 82),
    (1003, 902, 87);

Expected:

| student    | major        | grade |
| ---------- | -------------| ----- |
| Peter      | Architecture | 87    |
| Claude     | Computing    | 91.5  |

In other words: retrieve top grade student for each major.

Playground here.

If possible, without TOP,LIMIT.

If possible old ANSI SQL as well as using window functions.

Engine MySQL, but not required.

My approach #1:

-- average grade by student
select s.name as Student,  m.name as Major, avg(g.grade) as Average
         from student s
         inner join grades g on (s.id = g.student_id)
         inner join major m on (m.id = s.major_id)
         group by s.id

but John is not needed:

| Student | Major        | Average |
| ------- | ------------ | ------- |
| Claude  | Computing    | 91.5000 |
| John    | Computing    | 79.0000 |
| Peter   | Arquitecture | 87.0000 |

My approach #2:

-- Max average grade by career; lacks student
select a.major, max (a.average) as Average
    from (select s.name as Student,  m.name as Major, avg(g.grade) as average
         from student s
         inner join grades g on (s.id = g.student_id)
         inner join major m on (m.id = s.major_id)
         group by s.id) a
    group by a.major;           

but lacks student column.

| major        | Average |
| ------------ | ------- |
| Arquitecture | 87.0000 |
| Computing    | 91.5000 |

Thank you.

Answer 1

For older versions of MySQL...

SELECT x.* 
  FROM
     ( SELECT m.name major
            , s.name student
            , AVG(grade) avg_grade
         FROM major m
         JOIN student s
           ON s.major_id = m.id
         JOIN grades g
           ON g.student_id = s.id
        GROUP
           BY major
            , student
     ) x
  JOIN
     (
       SELECT major
            , MAX(avg_grade) avg_grade
         FROM 
            ( SELECT m.name major
                   , s.name student
                   , AVG(grade) avg_grade
                FROM major m
                JOIN student s
                  ON s.major_id = m.id
                JOIN grades g
                  ON g.student_id = s.id
               GROUP
                  BY major
                   , student
            )n
        GROUP
           BY major
      ) y
     ON y.major = x.major
    AND y.avg_grade = x.avg_grade

| major        | student | avg_grade |
| ------------ | ------- | --------- |
| Arquitecture | Peter   | 87.0000   |
| Computing    | Claude  | 91.5000   |

---

View on DB Fiddle

Answer 2

If you are running MySQL 8.0, you can do this with rank():

select *
from (
    select s.name as student, m.name as major, avg(g.grade) as average,
        rank() over(partition by m.id order by avg(g.grade) desc) rn
    from student s
    inner join grades g on s.id = g.student_id
    inner join major m on  m.id = s.major_id
    group by s.id, m.id
) t
where rn = 1

Notes:

• rank() allows ties

• parentheses around the join conditions are superfluous

Answer 3

You could combine your two queries:

select a.* FROM 
(        select s.name as Student, m.name as Major, avg(g.grade) as Average
         from student s
         inner join grades g on (s.id = g.student_id)
         inner join major m on (m.id = s.major_id)
         group by s.id
) a
INNER JOIN 
(select a.major, max (a.average) as Average
    from (select s.name as Student,  m.name as Major, avg(g.grade) as average
         from student s
         inner join grades g on (s.id = g.student_id)
         inner join major m on (m.id = s.major_id)
         group by s.id) a
    group by a.major   ) b
ON a.major=b.major and a.average=b.average