SQL Server Priority Ordering

Question

I have a table that contains tasks and I want to give these an explicit ordering based on the priority of the task. The only way I can think to do this is via an unique int column that indexes where the task is in term of the priority (i.e. 1 is top 1000 is low).

The problem is that say I wanted to update task and set its priority to a lower value , I would have to update all the other rows between its current value and its new value.

Can anyone suggest a better way of implementing this?

Answer 1

Use a real number value as the priority. You can always slide in a value between two existing values with something like newPri = task1Pri + (task2Pri - task1Pri)/2 where Task1 has the lower priority numeric value (which is probably the higher piority).

Corin points out that min and max priorities would have to be calculated for tasks inserted at the top or bottom of the priority list.

And joelhardi reminds us that a reorder process is a good idea to clean up the table from time to time.

Answer 2

Instead of creating an numbered column like you said, create a field called something like parent. Each row contains the pk of its parent item. When you want to move one item down just change its parent pk to the new one and the item(s) which reference it in their parent pk. Think singly linked lists.