[MySQL] Insert top 11 players for cp, fps, recordtype into another table
Code:
[22:58] IzNoGoD: hey
[22:58] IzNoGoD: wanna give some mysql help?
[23:00] kung foo man: hey
[23:00] kung foo man: just ask in forum?
[23:00] IzNoGoD: nah, this is way deeper than that
[23:00] IzNoGoD: just spent 1 hour trying to figure it out
[23:00] IzNoGoD: still nothing
[23:00] IzNoGoD: i now know the troublemaking part but still... no solution yet...
[23:01] IzNoGoD:
BEGIN
DECLARE done1 INT DEFAULT FALSE;
DECLARE current_id INT;
DECLARE cur1 CURSOR FOR SELECT player_id FROM checkpoint_statistics WHERE cp_id = checkpointnum AND fps = fps_in GROUP BY player_id ORDER BY MIN(recordtype) ASC LIMIT 11;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO current_id;
IF done1 THEN
LEAVE read_loop;
END IF;
INSERT IGNORE INTO checkpoint_statistics_temp_Caroline
SELECT * FROM checkpoint_statistics WHERE player_id = current_id AND fps = fps_in AND cp_id = checkpointnum AND recordtype =
(SELECT MIN(recordtype) FROM checkpoint_statistics WHERE player_id = current_id AND fps = fps_in AND cp_id = checkpointnum) ORDER BY recordtype ASC LIMIT 1;
END LOOP;
CLOSE cur1;
END
[23:01] IzNoGoD: you know this function?
[23:01] kung foo man: not yet i guess ^^
[23:01] IzNoGoD: ok
[23:01] IzNoGoD: what it SHOULD do
[23:01] IzNoGoD: it is called for every checkopint on a map
[23:01] IzNoGoD: for every fps
[23:01] IzNoGoD: and for every type of record
[23:02] IzNoGoD: then, it should find the top 11 players for that cp, fps and recordtype
[23:02] IzNoGoD: and insert them into another table
[23:02] IzNoGoD: it sorta works
[23:02] IzNoGoD: but it inserts the top 11 players alright
[23:03] IzNoGoD: but takes a seemingly random run_id for ALL checkpoints
[23:03] IzNoGoD: like for fizzy, it takes a random run_id on mp_dustville
[23:03] IzNoGoD: then adds only the values from THAT SPECIFIC run_id to the temp db
[23:04] IzNoGoD: which results in some records made with other run_ids being invisible
[23:04] IzNoGoD: if i remove the ORDER BY recordtype ASC LIMIT 1; everything is fine
[23:04] IzNoGoD: although there is too much data inserted into the temptable
[23:04] IzNoGoD: which i wanna avoid
Maybe somebody can disprove the bold text and answer the question via forum :D