SQL Server Order Priority

Question

I have a table containing tasks, and I want to give them explicit ordering based on the priority of the task. The only way I can do this is to use an int unique column that indexes where the task is in priority order (i.e. 1 - top 1000 - low).

The problem is that I want to update the task and set its priority to a lower value, I will have to update all the other lines between its current value and its new value.

Can anyone suggest a better way to implement this?

Answer 1

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

Corin points out that minimum and maximum priorities should be calculated for tasks inserted at the top or bottom of the priority list.

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

Answer 2

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

Answer 3

I like that Kevin answers best, but if you want a quick and dirty solution, just do it the way you already described, but instead of increasing by 1, increasing by 10 or 100 ... this way, if you you need to redefine priorities, you have room for maneuver between tasks.

Answer 4

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 UPDATE WHERE Priority> xxx, as you said.

Answer 5

If two tasks cannot have the same priority, I think this is what you need to do. But you can have priority and date columns and just sort both to get the correct order based on priority and last update if you allow duplication of priority.