Xrhrft

I have a table saving scores for a game that have 3 columns (more in practice, but it is easier for everyone this way):

userid   : the id of the user that got the score

timestamp: the time the score was gotten

score    : the score itself

The goal of the query is to delete rows so we have at most 10 scores per user.We also want to keep the most recent rows. So we need to delete the older ones until we get to 10.Note that some players may have less than 10 scores.

How would I manage to do this?

You can try below procedure first test it on local before going to production.

This consider that user has unique timestamp I have taken that as scored_at

Table Structure

show create table user_scoreG

*************************** 1. row ***************************

       Table: user_score

Create Table: CREATE TABLE `user_score` (

  `userid` int(11) DEFAULT NULL,

  `score` int(11) DEFAULT NULL,

  `scored_at` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

Procedure

DROP PROCEDURE IF EXISTS DeleteRecords;



DELIMITER $$



CREATE PROCEDURE `DeleteRecords`()

    BEGIN



    DECLARE nomore_userid   BOOLEAN DEFAULT FALSE;

    DECLARE user_id     INT;



    DECLARE cursor_delete_users CURSOR FOR 

    SELECT userid FROM user_score GROUP BY userid HAVING COUNT(userid) > 10;



    DECLARE CONTINUE HANDLER FOR NOT FOUND SET nomore_userid = TRUE;

    OPEN cursor_delete_users;



    REPEAT 



        FETCH cursor_delete_users INTO user_id;



        IF NOT nomore_userid THEN       

            SELECT COUNT(*) INTO @varCount FROM user_score WHERE userid = user_id;



            SET @limit =  @varCount - 10;



            SET @varSQL = CONCAT('DROP TABLE IF EXISTS tmp_user;');

            PREPARE statement FROM @varSQL;

            EXECUTE statement;

            DEALLOCATE PREPARE statement;



            SET @varSQL = CONCAT('CREATE TABLE tmp_user SELECT * FROM user_score WHERE userid =',user_id ,'  ORDER BY scored_at DESC

            LIMIT 10', ',',@limit,';');



            PREPARE statement FROM @varSQL;

            EXECUTE statement;

            DEALLOCATE PREPARE statement;



            DELETE a.*  FROM user_score a JOIN tmp_user b ON a.userid = b.userid and a.scored_at = b.scored_at;

        END IF; 



    UNTIL nomore_userid

    END REPEAT;





    END$$



DELIMITER ;

I found figured out an answer that works pretty well, though if you have a lot of users it may take some time to execute. I have yet to do any time testing with it. It also assumes you have an id field that uniquely identifies each score.

The way it works is in the inner query it finds up to the 10 most recent scores. Then deletes everything not returned by the inner query.

DELETE FROM score

WHERE id NOT IN (

  SELECT id

  FROM (

    SELECT *

    FROM score s1

    WHERE (

        SELECT COUNT(*)

        FROM score s2

        WHERE s1.userid = s2.userid

            AND s1.timestamp <= s2.timestamp

    ) <= 10 --Keep this many records

  ) foo

);

Rank the rows by timestamp, and delete any whose rank is greater than 10.

It's difficult to do it in one sentence because MySQL doesn't allows the LIMIT statement in subqueries, that's why we need a temp folder.

We need a temp table to get the oldest timestamp we will maintain

CREATE TABLE i_am_a_temp_folder (

`userid` INT(10) UNSIGNED NOT NULL,

`timestamp` DATETIME NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Getting the oldest timestamp (number 10) of the users which have 10 or more scores

INSERT INTO i_am_a_temp_folder (userid, `timestamp`)

  select c1.userid, c1.`timestamp`

    from score_table c1

    join score_table c2 

     on c1.userid= c2.useridand c1.`timestamp` <= c2.`timestamp`

    group by c1.userid, c1.`timestamp`

    having count(*) = 10;

Only on the users with more than 10 scores we will delete the older scores

DELETE c.* FROM score_table c

INNER JOIN i_am_a_temp_folder t

ON  c.userid= t.userid

AND c.`timestamp` < t.`timestamp`

Deleting the temp table

drop table i_am_a_temp_folder;

Table structure, example:

row_id  int(11) Auto Increment   

userid  int(11)  

score   int(11)  

updated_at  timestamp [CURRENT_TIMESTAMP]   

First: Insert a new record

INSERT INTO `user_score` (`userid`, `score`)

VALUES ('50', '80');

Second: Delete all rows start from position 10th, sorted by updated_at descending

DELETE FROM user_score 

WHERE row_id < ( SELECT row_id FROM 

                   (SELECT * FROM user_score 

                    ORDER BY updated_at DESC 

                    LIMIT 9,1) AS us) 

If we want too keep 10 records, the limit offset is 9

Delete from table where id not in ( select top 10 Id from table)