I have a test results table that looks like this
student_id test_id score 1 1 90 1 1 100 1 1 80
2 1 95 2 1 85 2 1 75
I need to create a query that would give me the average of the top 2 scores
per student per test. Following this example, student #1 would have an average of 95 (100 + 90)/2
for test #1 and student #2 would have an average of 90 (95 + 85)/2
Tricky, but doable.
SELECT a.student_id, a.test_id, avg(b.score), a.score AS second_highest, max(b.score) AS highest
FROM test_results a INNER JOIN test_results b ON a.student_id = b.student_id
WHERE a.score <= b.score
GROUP BY a.student_id, a.test_id, a.score
HAVING count(b.score) = 2;
I think this ought to work. To see how, try executing it by hand against the sample data. Basically, the WHERE restricts the join to look at combinations where the student has scores at least the value found in a.score, which is needed to rank the scores. The "group by" allows us to count how many scores are at least as high as the one from 'a'. And, the "HAVING" clause allows us to isolate scores in 'a' which are second-highest using that information; we then compute the average score that's at least as high as the second-highest value.
Bruce Feist
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
