Reputation: 129
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.
Upvotes: 0
Views: 194
Reputation: 33935
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 |
---
Upvotes: 1
Reputation: 222722
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
Upvotes: 2
Reputation: 2770
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
Upvotes: 1