5

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?

2
2

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
);
2
  • I don't think that the query fits the question. Oct 12 '14 at 1:25
  • You are right. I edited it to be correct. That's what I get for blindly replacing names. My solution needs a unique id column.
    – Lukiahas
    Oct 15 '14 at 19:12
1

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 ;
1

2019 - I'm a bit late to the party for this question, but I was looking for a solution to a similar problem and came up with my own answer.

First you need to add an id for every row:

rowid    : primary key, auto increment, standard stuff
userid   : the id of the user that got the score
timestamp: the time the score was gotten
score    : the score itself

You have to do it in 2 statements... first statement, you are finding the lowest id (rowid column). Set the offset to 1 less than the number you want to keep (10-1=9)

$scorersid = whatever user id you want to look for

SELECT rowid FROM tablename WHERE userid = $scorersid
ORDER BY timestamp DESC LIMIT 9,1

This returns the lowest id number of the rows we want to keep.

When you have this rowid:

$scorersid = from previous statement
$lowestid  = the lowest row id that we got

DELETE FROM tablename
WHERE userid = $scorersid AND rowid < $lowestid

Too very fast statements that you could run everytime you add a new score to the database

0

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

0

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;
0

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

-4

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

2
  • Only if IDs are in ascending order of age. Feb 20 '14 at 12:11
  • 6
    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. Feb 20 '14 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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