Best way to store ordering

[hessodreamy]

When I'm storing a list of items, along with the ordering that's been manually assigned, I normally do it like so

SELECT animal, preference FROM favourite_animals ORDER BY preference ASC

animal	|preference
---------------------------
dog	|	1
goat	|	2
monkey	|	3
unicorn	|	4

But it does get a bit cumbersome when changing the order, as you obviously have to also change the order of any items being displaced. But I really can't think of another way. It's not the worlds most pressing problem, but any ideas an another way to store this kind of data?

[r937]

suggestion 1 (i tried myself) -- number them by 100s

to move monkey between dog and goat, change its preference to 150

basically all you have to do is some maths on the higher and lower values which bracket where you want the row to be moved to

in the long term, if you have ~lots~ and ~lots~ of shuffling around, you might need to resequence the rows

suggestion 2 (have not tried myself) -- use FLOAT for the sort number

same maths, but no resequencing will ever be needed