Consider a Student table, and a Grades table. Grades table has grades for all the courses that the student took. I want to find the student who has the maximum average grade.
I would have used CTE for this problem, but it seems that MySQL doesn't provide CTE. I want to write a single query to find the student with maximum avg. grade, also avoiding writing the query, that gets avg. grades of all students, more than once.
A query that doesn't work is as follows (this might give more idea what I want to achieve):
select temp.st_name
from (select st1.student_name st_name, AVG(grade) avg_grade from Student st1, Grades grd1 where st1.student_id = grd1 .student_id group by st1.student_id, st1.sudent_name) temp
where temp.avg_grade = (select MAX(temp.avg_grade) from temp)
The above query gives the error: Table temp doesn't exist.
Is there any other way I can find the student with maximum avg. grade, avoiding to write inline-view statement twice?
Thanks!
No, you can't. Your options are to copy/paste that bad-boy into another subquery or create an actual view to query against.