Reorder records

[loydall]

Hello,

Lets say I have a table that looks like this:

ID | DisplayOrder | UserName
---------------------------
1 | 3 | Bob
2 | 1 | Tom
3 | 6 | Claire
4 | 5 | Sally
5 | 2 | John
6 | 4 | Tim

And I query the table and order by DisplayOrder.

I need an update that will move the display order up or down so, for example, if I want to move sally (currently in position 5) up to 4, I also need to move Tim (currently in 4) down to 5.

And obviously similar logic for moving records down.

Any idea how ot write an update for this?

Thanks.

 

[r937]

the way i would do it is to use float for the displayorder column

so to move Sally to between 3 and 4, change her displayorder to halfway between the entries on either side of where she's going, i.e. 3.5, and you're finished

note the key point: you're only updating one entry, period, and that's one of the advantages of this, it's way faster than any other scheme which involves renumbering one or more other entries (worst case scenario, you have to renumber the entire table)

you can keep doing it indefinitely, and you'll always be able to squeeze another entry in between any two existing entries, simply because of how floats work

one of the few instances where float is actually useful

r937.com | rudy.ca