CODEWITHSUNDEEP
mysql
Theresa
Theresa

Reputation: 95

mysql: find highest average score

I've written a query that pulls out the average score per player who was in inning 4:


SELECT batsmen.player_id, AVG(score)
FROM batsmen
WHERE batsmen.inning_no=4
GROUP BY player_id;

But now I need to find the highest average score. obviously i could look at the output, orderby and easily see the highest average, but i would like a query that pulls it out for me.

I assume somehow i need to use 


SELECT MAX(score);

with the result set received in my avg query above. Do I need to join/subquery?

Thanks Theresa

Upvotes: 1

Views: 5299

Answers (3)

SOA Nerd
SOA Nerd

Reputation: 941

I believe the SQL should be:

SELECT MAX(
 SELECT AVG(score)
 FROM batsmen
 WHERE batsmen.inning_no=4 
 GROUP BY player_id
);

Upvotes: -1

Patrick
Patrick

Reputation: 15717

Perhaps this will fill your needs:

SELECT player_id, AVG(score)
FROM batsmen
WHERE batsmen.inning_no=4
GROUP BY player_id
ORDER BY 2 DESC
LIMIT 1;

Upvotes: 3

symcbean
symcbean

Reputation: 48357

Not tested, but try this:

SELECT player_id, avg_score
FROM (
  SELECT batsmen.player_id, 
    AVG(score) avg_score
  FROM batsmen 
  WHERE batsmen.inning_no=4 
  GROUP BY player_id
)
ORDER BY avg_score DESC
LIMIT 0,1;

C.

Upvotes: 0

Related Questions