This must be a common problem but I haven't seen it discussed anywhere.

I need to enable my users to easily specify (and re-specify) a
position for each row
relative to the others above and below it. I therefore can't use the
primary key or other columns to sort by. I must use an extra integer
column where the sequence is defined by the integer.

Of course I could have the users enter a sequence number in each row
manually, but as new rows get inserted or reorganised, the previous
numbers will need to be readjusted and this is a very unsatisfactory
option.

I am using the Developer Express grid to drag and drop the rows and
they have given me code which (upon an insert or drag) automatically
recalculates the sequence in the grid, often for all the rows in the
dataset.

This is done on a row by row basis, so the process is very very slow
and gets slower as the number of rows in the dataset increases. Also,
in a multi-user situation I suspect this will be unworkable.

Does anyone have any suggestions for accomplishing this in a way which
numbering is automatic for the user, but which maintains the
performance and efficiency of a database with > 10,000 rows.

Any clues would be a great help.

Thanks