I have a simple table I created that was made from parts of other tables
that just has 2 fileds. The first field is an ID number and the second is a
score. Now each ID can have from 1 to 15 scores and what I need to do is for
each ID, get the average of the top 6 scores.
Is there a way to do this with a query or do I need to use a scripting
language to iterate through the IDs?
I know
select id, avg(score) from test group by id limit 6; does not work as it
only returns first 6 averages.
I can do a
create temporary table results select id, score from test where id = "1"
order by score desc limit 6;
Will give me the top six scores for id=1 but I would have to do this about
one hundred times to get all the ids.. Not very practicle.
Suggestions?
using mysql 3.23.39
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php