Reorder numbers

[icorbin]

Hi.

I'm building an app with rankings and I need to know how mySQL can automatically resort the numbers

for example:

1. apple
2. orange
3. banana

but when someone want to make orange #3, then banana moves to #2

example:

1. apple
2. banana
3. orange

any help on this subject would be great. Thanks.

[Lars_Berg]

You have to do the updates.

if moving down

update frutis set rank = rank - 1 where rank > 2 and <= 3

-- bigger than the old rank and 
-- less or equal to the new rank

update fruits set rank = 3 where fruit = 'orange'

moving up, very much the same