MySQL method to maintain sequentially numbered "top n" list

Question

CREATE TABLE item  ( link MEDIUMINT UNSIGNED PRIMARY KEY NOT NULL,
         title TEXT NOT NULL,
         rank INT UNSIGNED NOT NULL AUTO_INCREMENT,
          notes TEXT
             ) ENGINE = INNODB;

I'm having trouble implementing this. rank is a user given ranking that can be changed at any time, and items can be added and removed at any time. The problem is I would like the ranks to be unique, and always ordered between 1 and n (n being the number of rows in the table). I.E.: If a user changes the rank of item 5 to 2. The previous rank 2 item should become rank 3, the previous rank 3 goes to rank 4, and the previous rank 4 becomes the new rank 5. Similarly, for delete and create, all larger or smaller ranked items should cascade down one or up one respective of the operation.

Is there some kind of pattern or technique for easily implementing this?

Thanks for any help,

Michael

Answer 1

You can keep your items in a linked list:

id  parent  title   notes

1   0       Title 1 Note 1
2   1       Title 2 Note 2
3   2       Title 3 Note 3
4   3       Title 4 Note 4

and query like this:

SELECT  lv.*
FROM    (
        SELECT  @r AS _parent,
                @r := (
                SELECT  id
                FROM    t_list
                WHERE   parent = _parent
                ) AS id
        FROM    (
                SELECT  @r := 0
                ) vars,
                t_list
        ) li
JOIN    t_list lv
ON      lv.id = li.id

Moving an item (or even a block of items) in this design takes but three UPDATE operations (you change the parents of the item you are moving, of the item you are moving to and of the item you are moving from).

See this article in my blog for the details:

• Sorting lists