1

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?

  • 1
    Why don't you store the history in a different table? Or in the same table by adding a date column to the key? This is the relational way to solve this problem. Storing multiple values separated by commas in one field is asking for problems. – Colin 't Hart Sep 3 '15 at 13:16
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 !!!

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.