Recording history of ranks

Question

Once per day, this is being executed to rank everyone in the database based on points.

    SET @r=0;
    UPDATE ranks SET previousRank=rank, rank= @r:= (@r+1) ORDER BY points DESC;

The field previousRank contains their previous rank. However, we are now wanting to record the history of the rank.

If we change previousRank to a STRING, is it possible to modify the query to append "rank," to the end of field previousRank instead of replace it? For example, it would look like this: 21,22,20,18,24,.

If so, how would this be done, and are there any steps that can be taken to remove the end comma without slowing the query?

Answer 1

If you want previousRank to end with a comma

SET @r=0;
UPDATE ranks SET
    previousRank=CONCAT(previousRank,rank,','),
    rank= @r:= (@r+1)
    ORDER BY points DESC
;

If you do not want previousRank to end with a comma

SET @r=0;
UPDATE ranks SET
    previousRank=IF(previousRank='',rank,CONCAT(previousRank,',',rank)),
    rank= @r:= (@r+1)
    ORDER BY points DESC
;

Give it a Try !!!