ansaurus

Question

Answer 1

A:

I believe the SQL should be:

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

SOA Nerd 2010-03-02 12:19:00

Answer 2

A:

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.

symcbean 2010-03-02 12:19:14

Thanks but it didn't pick out the highest average. I changed a little bit and I get the correct output using "SELECT player_id, MAX(average)FROM ( SELECT AVG(score) AS average, player_id FROM batsmen where batsmen.inning_no=4 group by batsmen.player_id ) AS highest_average;" BUT even though the average is correct - the player ID is incorrect (it should be 103 but it is showing as 1). Thanks

Theresa 2010-03-02 12:55:22

I can see why your solution doesn't work as expected, but I don't understand why mine doesn't give you what you asked for. Try it again without the LIMIT and check the result.You could extend your method by using the MAX(CONCAT(LPAD trick to create a single composite field out of the two values (try Google for examples)

symcbean 2010-03-03 11:57:42

Answer 3

+1 A:

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;

Patrick 2010-03-02 13:34:40

Hi,Thanks very much - this is the one!

Theresa 2010-03-03 06:34:06

ansaurus

tags:

views:

answers:

mysql: find highest average score

related questions