Dynamically reorder column values in a SQL Server table

Question

I have a SQL Server table for user skills with priority order for each user as this:

+----+------+----------+----------+
| ID | user | skill    | priority |
+----+------+----------+----------+
| 1  | foo  | swimming | 1        |
+----+------+----------+----------+
| 2  | foo  | running  | 2        |
+----+------+----------+----------+
| 3  | foo  | hunting  | 3        |
+----+------+----------+----------+
| 4  | boo  | swimming | 1        |
+----+------+----------+----------+
| 5  | moo  | swimming | 1        |
+----+------+----------+----------+
| 6  | moo  | running  | 2        |
+----+------+----------+----------+

How can I write SQL code to re-order the priority column values (an integer) for all skills for a user when the priority value is changed for one of the skills?

For example: for user "foo" I will change the priority of the skill "swimming" from (1) to (2); the update statement must also change the priority for all other skills for that user, in a dynamic way.

So in that example "swimming" will be priority (2) instead of (1), running will be (1) instead of (2), and the others will remain the same.

Some other examples will be helpful, especially for edge cases. For example: if the priority for foo/swimming is changed to 0, should foo/running be 1? What if foo/swimming is changed to 5? — Richard II, Nov 01 '19 at 13:09
SQL Server does not store or retrieve items in any particular order. Queries are done in parallel using different cores in the microprocessor so you must always use an OrderBy to get results sorted. — jdweng, Nov 01 '19 at 13:09
@jdweng, this question doesn't have anything to do with the order of how rows are stored or retrieved. It's about assigning new priority values for each row for a user , whenever one of the priority values for that user is changed. — Richard II, Nov 01 '19 at 13:11
@RichardII for edge values, min value is (1) and max value is the count of total skills (3 skills for this user then max value for priority is 3) and priority value is not editable by user, only the order of the skills which will affect the priority value server side. — Sherif Riad, Nov 01 '19 at 13:58
@PeterB as you might have noticed I'm asking for the best approach because I couldn't figure out how to achieve that in the first place, so I have no wrong code or complicated queries that need to be simplified. — Sherif Riad, Nov 01 '19 at 13:59
@Sherif, I assume you want a SQL approach, not a C# approach? — HTTP 410, Nov 01 '19 at 15:59
[This](https://stackoverflow.com/a/15858191/92546) answer covers deleting, inserting and swapping. Swapping, moving up and moving down is covered [here](https://stackoverflow.com/a/48329605/92546). — HABO, Nov 01 '19 at 19:02
@HABO, that first answer deals with reordering after a row deletion. This question is about plain reordering (no row deletion). — HTTP 410, Nov 01 '19 at 19:05
@HABO, as for the second answer you linked, that SQL code is really lengthy, complex, and generally nasty. I'm not even sure it is correct. — HTTP 410, Nov 01 '19 at 19:17

HTTP 410 · Accepted Answer · 2019-11-02T15:22:38.613

In this answer I'm assuming that you want to do this in SQL, not in C#. Based on that assumption, these two SQL statements inside a single transaction increase the priority of the specified skill by 1.

'Set @User to the required user and @Skill to the required skill.

'Decrease the priority of the user's skill above the specified skill.
UPDATE MyTable
SET    priority = priority + 1
WHERE  user = @User 
AND    priority = (SELECT priority - 1 
                   FROM MyTable 
                   WHERE user = @User
                   AND skill = @Skill)

'Increase the specified skill's priority.
UPDATE MyTable
SET    priority = priority - 1
WHERE  user = @User
AND    skill = @Skill
AND    priority > 1

In a similar fashion, these two SQL statements increase the specified skill to the specified priority.

'Set @User to the required user and @Skill to the required skill.
'Set @NewPriority to the new priority.

'Decrease the higher-prioritised skills.
UPDATE MyTable
SET    priority = priority + 1
WHERE  user = @User 
AND    priority >= @NewPriority 
AND    priority < (SELECT priority
                   FROM MyTable 
                   WHERE user = @User
                   AND skill = @Skill)

'Set the specified skill's priority as requested.
UPDATE MyTable
SET    priority = @NewPriority 
WHERE  user = @User
AND    skill = @Skill
AND    priority > 1

And these three SQL statements move the specified skill to the specified priority.

'Set @User to the required user and @Skill to the required skill.
'Set @NewPriority to the new priority.

'Decrease the higher-prioritised skills to
'handle case where new priority is higher.
UPDATE MyTable
SET    priority = priority + 1
WHERE  user = @User 
AND    priority >= @NewPriority 
AND    priority < (SELECT priority
                   FROM MyTable 
                   WHERE user = @User
                   AND skill = @Skill)

'Increase the lower-prioritised skills to
'handle case where new priority is lower.
UPDATE MyTable
SET    priority = priority - 1
WHERE  user = @User 
AND    priority <= @NewPriority 
AND    priority > (SELECT priority
                   FROM MyTable 
                   WHERE user = @User
                   AND skill = @Skill)    

'Set the specified skill's priority as requested.
UPDATE MyTable
SET    priority = @NewPriority 
WHERE  user = @User
AND    skill = @Skill

it seems (after further clarification in question comments) that OP wants to be able to issue an update to the priority for one skill to be anywhere within the range 1-n where the user has "n" skills. Then the priority of all the other skills should be adjusted up or down as necessary to fill in the rest of the values in that range 1-n. — Richard II, Nov 01 '19 at 14:39
@RoadWarrior from what I understand from your code, I should use the last part (the three statements) in my stored procedure as it replaces the old answer in the 2 first parts? — Sherif Riad, Nov 01 '19 at 18:32
@Sherif, that's correct if you want to re-prioritise a skill to any position (as opposed to the other more simple use-cases I mentioned in the first two code sections). — HTTP 410, Nov 01 '19 at 18:49
@SherifRiad And take note of "SQL statements inside a single transaction" if you want to make the operation atomic. — HABO, Nov 01 '19 at 19:55

Dynamically reorder column values in a SQL Server table

1 Answers1