mysqlinline-view

Is there a way to avoid writing inline views more than once in MySQL


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!


Solution

  • No, you can't. Your options are to copy/paste that bad-boy into another subquery or create an actual view to query against.