Hi All,

I have a problem to order a set of data.

I have a given set of data.
Initialy it is ordered by a logic over some columns. (Like SELECT * FROM myTable Order 2,4,6)

My customer need to change this order later by either:
-moving the position of record(s)
-insert a record(s) between given records (or on top / bottom)

My basic idea was to store the order in a separate, nonvisible field.

The UI works on a view on a multiuser database.

My problem is:

Since I have never done a multiline TABLEUPDATE, I would prefer to change only the moved record.

If I move are insert a record, there must be a gap between two records.

If I initaly store order as 1,2,3,... I have to come between the values.

In case 1,2 are numerical I can put something at 1.5. but if I have later to put something between 1 and 1.5 I will go to 1.25. This way seems to be limited because every new division by 2 will give me a new decimal place.


I'm not happy with this.

My question is:
Is there a common way to solve problems like this?

Would it be easier to renumber the whole view and TABLEUPDATE it (and also handle conflicts) than to build up a logic to calculate a number the fits between two given numbers?

If calculation is easier is there a good way to calculate?


TIA

Agnes