Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
I believe approach in answer I got in my question Query: All employee with sum of salary > 500 Can be helpful. –  Grijesh Chauhan Feb 20 at 11:06
add comment

3 Answers

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_score\G
*************************** 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 ;
share|improve this answer
add comment

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

share|improve this answer
add comment

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

share|improve this answer
    
Only if IDs are in ascending order of age. –  Colin 't Hart Feb 20 at 12:11
3  
This answer has several failures. It uses TOP (which is an SQL-Server thing, not MySQL). It uses TOP without ORDER BY which would result in indeterminate results. It doesn't take into account the timestamp, as specified by the OP. And lastly it would leave only 10 rows in the table while the OP wants to leave 10 rows per user. –  ypercube Feb 20 at 12:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.