CODEWITHSUNDEEP
mysql
Beginner questions
Beginner questions

Reputation: 343

in MYSQL return Student name with highest score overall

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

Answers (2)

Moin
Moin

Reputation: 11

Use this query:

SELECT studentName, max( mark ) as maxMark FROM `student` GROUP BY studentName

Upvotes: 0

Barranka
Barranka

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:

  1. Calculate the average for each student
  2. Get the highest average
  3. Filter the student with the highest average

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

Related Questions