Receiving from the top 10 students from each school

Question

We have a school district with 38 elementary schools. Children passed the test. The average values ​​for schools are widely scattered, but I want to compare the average values ​​ONLY TOP 10 students from each school.

Requirement: Use only temporary tables.

I made this a very time-consuming, error-prone way, as follows. (sch_code = e.g. 9043; - schabbrev = e.g. "Carter"; - totpct_stu = e.g. 61.3)

DROP TEMPORARY TABLE IF EXISTS avg_top10 ; CREATE TEMPORARY TABLE avg_top10 ( sch_code VARCHAR(4), schabbrev VARCHAR(75), totpct_stu DECIMAL(5,1) ); INSERT INTO avg_top10 SELECT sch_code , schabbrev , totpct_stu FROM test_table WHERE sch_code IN ('5489') ORDER BY totpct_stu DESC LIMIT 10; -- I do that last query for EVERY school, so the total -- length of the code is well in excess of 300 lines. -- Then, finally... SELECT schabbrev, ROUND( AVG( totpct_stu ), 1 ) AS top10 FROM avg_top10 GROUP BY schabbrev ORDER BY top10 ; -- OUTPUT: ----------------------------------- schabbrev avg_top10 ---------- --------- Goulding 75.4 Garth 77.7 Sperhead 81.4 Oak_P 83.7 Spring 84.9 -- etc... 

Question: So this works, but there is no much better way to do this?

Thanks!

PS - It seems like homework, but this, well ... is real.

Answer 1

Using this method .

 select sch_code, schabbrev, ROUND( AVG( totpct_stu ), 1 ) AS top10 from (select sch_code, schabbrev, totpct_stu, @num := if(@group = sch_code, @num + 1, 1) as row_number, @group := sch_code as dummy from test_table order by sch_code, totpct_stu desc) as x where row_number <= 10 GROUP BY sch_code, schabbrev