Reputation: 343
i.e. a table called students (Assume there is only one student with that name, e.g. there isnt two students called John Smith) with studentName, studentScore, subject
assume table has this
John Smith 40 maths
bob grey 20 english
anne hank 23 english
John Smith 30 english
anne grey 10 maths
I am trying to find the most highest scored student by calculating the maximum of averages of all students
this here will select the highest average but not the student name with that average:
SELECT MAX(avgStudentScore)
FROM (SELECT AVG(studentScore) AS avgStudentScore FROM students GROUP BY studentName) t
thx
Upvotes: 0
Views: 3281
Reputation: 11
Use this query:
SELECT studentName, max( mark ) as maxMark FROM `student` GROUP BY studentName
Upvotes: 0
Reputation: 21067
If all you need is the name, you can do something like this:
select studentName, avg(studentScore) as avgStudentScore
from students
group by studentName
order by avgStudentScore desc
limit 1
This will return only the first row of the query. Since it's ordered by avgStudentScore
, it will return the student with the top average.
The above solution is the simplest and fastests, but it's not the only one. If you want to do it "the hard way" (with subqueries), what you need to do is:
So... let's do it the hard way ;)
select a.studentName
from
(
select studentName, avg(studentScore) as avgStudentScore
from students
group by studentName
) as a
where
a.avgStudentScore = (
select max(avgStudentScore)
from (
select avg(studentScore) as avgStudentScore
from students
group by studentName
) as a
)
Notice that with this approach the final result might not be unique (i.e. there may be one ore more students with the same average and that average is the highest).
Upvotes: 3