I have the following test CASE where I'm trying to find the person with the highest average for each course.
I have 3 courses set up yet I'm only returning 1 row. Can someone please tell me what I'm doing wrong and show me how to fix the issue.
Thanks in advance for your expertise and all that respond.
CREATE TABLE students(student_id, first_name, last_name) AS
SELECT 1, 'Faith', 'Aaron' FROM dual UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM dual UNION ALL
SELECT 3, 'Leslee', 'Altman' FROM dual UNION ALL
SELECT 4, 'Patty', 'Kern' FROM dual UNION ALL
SELECT 5, 'Beth', 'Cooper' FROM dual;
CREATE TABLE courses(course_id, course_name) AS
SELECT 1, 'Geometry' FROM dual UNION ALL
SELECT 2, 'Trigonometry' FROM dual UNION ALL
SELECT 3, 'Calculus' FROM DUAL;
CREATE TABLE grades(student_id,
course_id, grade) AS
SELECT 1, 1, 75 FROM dual UNION ALL
SELECT 1, 1, 81 FROM dual UNION ALL
SELECT 1, 1, 76 FROM dual UNION ALL
SELECT 2, 1, 100 FROM dual UNION ALL
SELECT 2, 1, 95 FROM dual UNION ALL
SELECT 2, 1, 96 FROM dual UNION ALL
SELECT 3, 1, 80 FROM dual UNION ALL
SELECT 3, 1, 85 FROM dual UNION ALL
SELECT 3, 1, 86 FROM dual UNION ALL
SELECT 4, 1, 88 FROM dual UNION ALL
SELECT 4, 1, 85 FROM dual UNION ALL
SELECT 4, 1, 91 FROM dual UNION ALL
SELECT 5, 1, 98 FROM dual UNION ALL
SELECT 5, 1, 74 FROM dual UNION ALL
SELECT 5, 1, 81 FROM dual UNION ALL
SELECT 1, 2, 95 FROM dual UNION ALL
SELECT 1, 2, 81 FROM dual UNION ALL
SELECT 1, 2, 96 FROM dual UNION ALL
SELECT 2, 2, 88 FROM dual UNION ALL
SELECT 2, 2, 85 FROM dual UNION ALL
SELECT 2, 2, 96 FROM dual UNION ALL
SELECT 3, 2, 60 FROM dual UNION ALL
SELECT 3, 2, 75 FROM dual UNION ALL
SELECT 3, 2, 86 FROM dual UNION ALL
SELECT 4, 2, 88 FROM dual UNION ALL
SELECT 4, 2, 85 FROM dual UNION ALL
SELECT 4, 2, 71 FROM dual UNION ALL
SELECT 5, 2, 100 FROM dual UNION ALL
SELECT 5, 2, 95 FROM dual UNION ALL
SELECT 5, 2, 100 FROM dual UNION ALL
SELECT 1, 3, 86 FROM dual UNION ALL
SELECT 1, 3, 87 FROM dual UNION ALL
SELECT 1, 3, 96 FROM dual UNION ALL
SELECT 2, 3, 88 FROM dual UNION ALL
SELECT 2, 3, 95 FROM dual UNION ALL
SELECT 2, 3, 96 FROM dual UNION ALL
SELECT 3, 3, 90 FROM dual UNION ALL
SELECT 3, 3, 75 FROM dual UNION ALL
SELECT 3, 3, 86 FROM dual UNION ALL
SELECT 4, 3, 87 FROM dual UNION ALL
SELECT 4, 3, 85 FROM dual UNION ALL
SELECT 4, 3, 81 FROM dual UNION ALL
SELECT 5, 3, 90 FROM dual UNION ALL
SELECT 5, 3, 72 FROM dual UNION ALL
SELECT 5, 3, 55 FROM dual;
with temp as
(select s.student_id
, s.first_name
, s.last_name
, c.course_name
, round(avg(g.grade), 1) as student_avg
, rank() over (order by c.course_name, avg(g.grade) desc) rnk
from students s join grades g on s.student_id = g.student_id
join courses c on c.course_id = g.course_id
group by s.student_id, s.first_name, s.last_name, c.course_name
)
select student_id, first_name, last_name, course_name, student_avg
from temp
where rnk <= 1
order by rnk;