Getting the avg of the top 10 students from each school

[mrherman]

Hi all --

 

We have a school district with 38 elementary schools.  The kids took a test.  The averages for the schools are widely dispersed, but I want to compare the averages of JUST THE TOP 10 students from each school.

 

Requirement: use temporary tables only.

 

I have done this in a very work-intensive, error-prone sort of 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 avg_top10
  FROM avg_top10
GROUP
    BY schabbrev
ORDER
    BY avg_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 isn't there a lot better way to do this?

 

Thanks!

[mrherman]

Moved to another board.  Thanks anyway.