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

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.

Answer 2

if no two tasks can have the same priority then I think that is what you have to do. But you could have a priority and a datemodified column and just sort by both to get the right order based on priority and last update if you allow priority to be duplicated.

Answer 3

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 4

I like Kevin's answer best, but if you want a quick-and-dirty solution, just do it the way you've already described, but instead of incrementing by 1, increment by 10 or 100... that way if you need to re-prioritize, you have some wiggle room between tasks.

Answer 5

I would assign only a small number of values (1..10) and then ORDER BY Priority DESC, DateCreated ASC. If you need to have different priorities for each task you need to UPDATE WHERE Priority > xxx like you said.