Storing sort order in mySQL

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
DavJans
Posts: 228
Joined: Thu Dec 12, 2013 4:21 pm
Location: Spokane, WA USA

Storing sort order in mySQL

Post by DavJans » Thu Mar 09, 2017 10:36 pm

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?
"Det bästa stället att hitta en hjälpande hand är i slutet av din egen arm"

MaxV
Posts: 1574
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Storing sort order in mySQL

Post by MaxV » Wed Mar 15, 2017 5:49 pm

To me: OPTION 2

Do you know this https://en.wikipedia.org/wiki/Tower_of_Hanoi ?
It's a similar question.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

SparkOut
Posts: 1935
Joined: Sun Sep 23, 2007 4:58 pm

Re: Storing sort order in mySQL

Post by SparkOut » Wed Mar 15, 2017 9:25 pm

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

Code: Select all

SET @newid=0;
UPDATE tablename SET priority_key_id=(@newid:=@newid+100000) ORDER BY priority_key_id;
(adapted from code here: http://stackoverflow.com/questions/1024 ... l-database)
Test first on a non-critical database

Post Reply

Return to “Databases”