I'm trying to expand a little on a statement that I received help with last week. As you can see, I'm setting up a temporary table and inserting rows of student data from a recently administered test for a few dozen schools. When the rows are inserted, they are sorted by the score (totpct_stu, high to low) and the row_number is added, with 1 representing the highest score, etc.
I've learned that there were some problems at school #9999 in SMITH's class (every student made a perfect score and they were the only students in the district to do so). So, I do not want to import SMITH's class.
As you can see, I DELETED SMITH's class, but this messed up the row numbering for the remainder of student at the school (e.g., high score row_number is now 20, not 1).
How can I modify the INSERT statement so as to not insert this class?
Thanks!
DROP TEMPORARY TABLE IF EXISTS avgpct ;
CREATE TEMPORARY TABLE avgpct_1
( sch_code VARCHAR(3),
schabbrev VARCHAR(75),
teachername VARCHAR(75),
totpct_stu DECIMAL(5,1),
row_number SMALLINT,
dummy VARCHAR(75)
);
-- ----------------------------------------
INSERT INTO avgpct
SELECT sch_code
, schabbrev
, teachername
, totpct_stu
, @num := IF( @GROUP = schabbrev, @num + 1, 1 ) AS row_number
, @GROUP := schabbrev AS dummy
FROM sci_rpt
WHERE grade = '05' AND
totpct_stu >= 1 -- has a valid score
ORDER
BY sch_code, totpct_stu DESC ;
-- ---------------------------------------
-- select * from avgpct ;
-- ---------------------------------------
DELETE
FROM avgpct_1
WHERE sch_code = '9999' AND
teachername = 'SMITH' ;
