Skip to content

Instantly share code, notes, and snippets.

@istarkov

istarkov/question.md

Last active March 5, 2018 22:00
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save istarkov/dd810c9e90ef9996ef8c06810a5a2fc5 to your computer and use it in GitHub Desktop.
Save istarkov/dd810c9e90ef9996ef8c06810a5a2fc5 to your computer and use it in GitHub Desktop.
Download ZIP
Need help
Raw
question.md

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