Skip to content

Instantly share code, notes, and snippets.

@istarkov

istarkov/question.md

Last active Mar 5, 2018
Embed
What would you like to do?
Need help

I want to hold ordering of some items in SQL, like field in a table index: 0,1,2,3,4,... and I have operation moveItem

If I want to move item from index: 1 to index: 3 I can do

delete from table where index = 1;
update table set index = index - 1 where index > 1
update table set index = index + 1 where index >=3
insert into table (index) values (3)

from first sight all is ok, but we live in real world and if anything have no constraint it will be broken.

I know I can add constraints that index is unique, that index is not null, but as I know I can't add constraint that index should have no holes.

With holes in index i.e index: 0,1,4,5 simple algorithm above is broken.

So the question is - may be other simple algorithm exists for holding item ordering?

(The problems with google search that index and order is SQL keywords)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.