Dynamic list

[Ben]

Hi all,

I have a table with a column called priority. Each record (task) in this
table has a priority associated with it. For example, if there are 100 items
in the list, there are 100 priorities, from 1, 2, 3, ..., 100

However, there are times, when a user needs to re-prioritize some tasks, for
example:

Current Priority New Priority
1
2
3
4 1
5
6 5
7
8 2

that is, if there are 8 tasks, and I want re-prioritize, changing priority
from 4 to 1, 6 to 5 and 8 to 2. I want to know if there is a way in which
the rest of the tasks would automatically re-number themselves to :

Current Priority New Priority
1 3
2 4
3 6
4 1
5 7
6 5
7 8
8 2



Can you suggest a way in which I can dynamically re-number the priorities
based on what has been changed? That is the next priority would get shift
down by 1 in priority. Thanks in advance for sharing your thoughts.


Ben

 

[Tom van Stiphout]

On Tue, 13 May 2008 20:13:00 -0700, Ben

No, there is no automated or built-in way to do this. You will have to
run your own.
In your first example you are changing prio from 4 to 1. Run three
update statements:

'Make room for #1.
update MyTable
set Prio=Prio+1
where Prio >= 1

'Move #4 to #1
update Prio set Prio=1
where Prio=4

'Close the gap at #4.
update Prio set Prio = Prio-1
where Prio > 4

You probably see a pattern emerge that can be used for the other
changes as well.

A totally different approach works with using Double rather than
Integer for Prio. For example say you want to move #6 in front of #2.
Just give that record a Prio of 1.5. If you want another one in front
of Prio=2, that would be (2.0 + 1.5)/2 = 1.75, etc.
Because a double has 15 decimals of precision you can do this for
quite a while. The smart programmer anticipates 16 moves, and will
then rebalance the Prio values, for example going 1000, 2000, 3000,
etc. The wider the gaps the less often rebalancing is needed.

-Tom.

 

[Ben]

Tom,

Thanks.

Ben

--

On Tue, 13 May 2008 20:13:00 -0700, Ben

No, there is no automated or built-in way to do this. You will have to
run your own.
In your first example you are changing prio from 4 to 1. Run three
update statements:

'Make room for #1.
update MyTable
set Prio=Prio+1
where Prio >= 1

'Move #4 to #1
update Prio set Prio=1
where Prio=4

'Close the gap at #4.
update Prio set Prio = Prio-1
where Prio > 4

You probably see a pattern emerge that can be used for the other
changes as well.

A totally different approach works with using Double rather than
Integer for Prio. For example say you want to move #6 in front of #2.
Just give that record a Prio of 1.5. If you want another one in front
of Prio=2, that would be (2.0 + 1.5)/2 = 1.75, etc.
Because a double has 15 decimals of precision you can do this for
quite a while. The smart programmer anticipates 16 moves, and will
then rebalance the Prio values, for example going 1000, 2000, 3000,
etc. The wider the gaps the less often rebalancing is needed.

-Tom.