--- In firebird-support@yahoogroups.com, "mlq97" <mlq@...> wrote:

>
> 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.

This is your problem, don't define it as an integer. Use a floating
point number. To insert a record between two records, average the
prior and subsequent values. Depending on just how dynamic it is, you
may need to from time-to-time resequence the entire table.

This strategy means you only need to look at the prior and subsequent
record. Your initial lowest value should be 1, and the highest value
should be a generator driven value. To Add it to the top, half the
previous first record. To Add to the bottom, grab a new value from the
generator.

eg

Seq
1
2
3
4
5

To add a record between 3 and 4, give it the sequence 3.5.

Seq
1
2
3
3.5
4
5

To add a record between 3 and 3.5, give it the sequence 3.25 etc.

Adam