kNF67nZq

1userid   : the id of the user that got the score
2timestamp: the time the score was gotten
3score    : the score itself
4	
5show create table user_scoreG
6*************************** 1. row ***************************
7       Table: user_score
8Create Table: CREATE TABLE `user_score` (
9  `userid` int(11) DEFAULT NULL,
10  `score` int(11) DEFAULT NULL,
11  `scored_at` datetime DEFAULT NULL
12) ENGINE=InnoDB DEFAULT CHARSET=utf8
131 row in set (0.00 sec)
14	
15DROP PROCEDURE IF EXISTS DeleteRecords;
16
17DELIMITER $$
18
19CREATE PROCEDURE `DeleteRecords`()
20    BEGIN
21
22    DECLARE nomore_userid   BOOLEAN DEFAULT FALSE;
23    DECLARE user_id     INT;
24
25    DECLARE cursor_delete_users CURSOR FOR 
26    SELECT userid FROM user_score GROUP BY userid HAVING COUNT(userid) > 10;
27
28    DECLARE CONTINUE HANDLER FOR NOT FOUND SET nomore_userid = TRUE;
29    OPEN cursor_delete_users;
30
31    REPEAT 
32
33        FETCH cursor_delete_users INTO user_id;
34
35        IF NOT nomore_userid THEN       
36            SELECT COUNT(*) INTO @varCount FROM user_score WHERE userid = user_id;
37
38            SET @limit =  @varCount - 10;
39
40            SET @varSQL = CONCAT('DROP TABLE IF EXISTS tmp_user;');
41            PREPARE statement FROM @varSQL;
42            EXECUTE statement;
43            DEALLOCATE PREPARE statement;
44
45            SET @varSQL = CONCAT('CREATE TABLE tmp_user SELECT * FROM user_score WHERE userid =',user_id ,'  ORDER BY scored_at DESC
46            LIMIT 10', ',',@limit,';');
47
48            PREPARE statement FROM @varSQL;
49            EXECUTE statement;
50            DEALLOCATE PREPARE statement;
51
52            DELETE a.*  FROM user_score a JOIN tmp_user b ON a.userid = b.userid and a.scored_at = b.scored_at;
53        END IF; 
54
55    UNTIL nomore_userid
56    END REPEAT;
57
58
59    END$$
60
61DELIMITER ;
62	
63DELETE FROM score
64WHERE id NOT IN (
65  SELECT id
66  FROM (
67    SELECT *
68    FROM score s1
69    WHERE (
70        SELECT COUNT(*)
71        FROM score s2
72        WHERE s1.userid = s2.userid
73            AND s1.timestamp <= s2.timestamp
74    ) <= 10 --Keep this many records
75  ) foo
76);
77	
78CREATE TABLE i_am_a_temp_folder (
79`userid` INT(10) UNSIGNED NOT NULL,
80`timestamp` DATETIME NOT NULL
81) ENGINE=InnoDB DEFAULT CHARSET=utf8;
82	
83INSERT INTO i_am_a_temp_folder (userid, `timestamp`)
84  select c1.userid, c1.`timestamp`
85    from score_table c1
86    join score_table c2 
87     on c1.userid= c2.useridand c1.`timestamp` <= c2.`timestamp`
88    group by c1.userid, c1.`timestamp`
89    having count(*) = 10;
90	
91DELETE c.* FROM score_table c
92INNER JOIN i_am_a_temp_folder t
93ON  c.userid= t.userid
94AND c.`timestamp` < t.`timestamp`
95	
96drop table i_am_a_temp_folder;
97	
98row_id  int(11) Auto Increment   
99userid  int(11)  
100score   int(11)  
101updated_at  timestamp [CURRENT_TIMESTAMP]
102	
103INSERT INTO `user_score` (`userid`, `score`)
104VALUES ('50', '80');
105	
106DELETE FROM user_score 
107WHERE row_id < ( SELECT row_id FROM 
108                   (SELECT * FROM user_score 
109                    ORDER BY updated_at DESC 
110                    LIMIT 9,1) AS us)