LiveCode Forums.

I have done some research into this, and settled on 2 options, maybe someone here has a better 3rd option for me.

pretend we have a table like this

Priority | Itemnumber
100000 | Item 1
200000 | Item 2
300000 | Item 3

Now we want to move Item 3 between item 1 and 2

Option 1
This option is limited to how many times you can re-prioritize.

get the next 2 lower priority numbers of item 3 and add them together and then divide by 2 getting 150000
set priority of Item 3 to 150000

Option 2
This option takes one more query

get the next lower priority of Item 3
set the priority of Item 3 to 200000
set the priority of Item 2 to 300000

Is there a better Option 3?

To me: OPTION 2

Do you know this https://en.wikipedia.org/wiki/Tower_of_Hanoi ?
It's a similar question.

Not sure about the best idea, but if the table is not too large and the overhead is not too much then option 1 with a reindexing might be "neatest" in MySQL:

Change the priority of item 3 to fall between 100000 and 200000.

Then run the SQL query(adapted from code here: http://stackoverflow.com/questions/1024 ... l-database)
Test first on a non-critical database