How to rearrange "sort order" column after changing the rows' sort order

Admin 1
Viewed 517

I have a table with many lines and a column call "order" which goes from 1 to XX depends on how many line i have in my table.

The thing is, if i want to move the line with the "order" 1 to the position 3, i need the other "order" from other line to rearange themselves.

For example, if i move the "order" 1 with a "3", all the number after will have to increase of 1 and the second will have to be "order" 1 now.

I hope that i was clear.

Thanks for your help !

EDIT / SAMPLE DATA

Name   |  Order
Line 1 |  1
Line 2 |  2
Line 3 |  3
Line 4 |  4

If i update the order of "Line 1" with 4 instead of 1, i want the other to have their order update !

Name   |  Order
Line 2 |  1
Line 3 |  2
Line 4 |  3
Line 1 |  4

Same thing if i update the order of "line 4" with a 1 !

Name   |  Order
Line 4 |  1
Line 1 |  2
Line 2 |  3
Line 3 |  4

Same thing if i update the order of "line 3" with a 2 for example !

Name   |  Order
Line 1 |  1
Line 3 |  2
Line 2 |  3
Line 4 |  4
sorting tsql sql-server sql
2 Answers

Try this approach

DECLARE @tbl TABLE([Name] VARCHAR(100),[Order] INT);
INSERT INTO @tbl VALUES
 ('Line 1',1) 
,('Line 2',2) 
,('Line 3',3)
,('Line 4',4);

DECLARE @OldPos INT=3, @NewPos INT=2;

WITH cte AS
(
    SELECT [Name]
          ,[Order]
          ,ROW_NUMBER() OVER(ORDER BY CASE WHEN [Order]=@OldPos 
                                           THEN CAST(@NewPos AS FLOAT) 
                                                + CASE WHEN @OldPos<@Newpos 
                                                       THEN 0.5 ELSE -0.5 END 
                                           ELSE CAST([Order] AS FLOAT) END) AS ComputedPos 
    FROM @tbl 
)
UPDATE cte SET [Order] = ComputedPos
WHERE [Order]<>ComputedPos;

SELECT * FROM @tbl ORDER BY [Order];

This uses an updateable CTE. The comment by Damian_The_Unbeliever gave me the idea without the need to change the order's data type.

The function ROW_NUMBER will do some computation in its OVER clause. If the [Order] has the right input value we will set the new position between the new neighbours by adding 0.5 or -0.5 to the new position. This depends on the direction you are moving your row.

user_50892040

You can use a CASE statement:

UPDATE ...
SET [Order] = [Order] + CASE
  WHEN [Order] = @OLDPOS THEN @NEWPOS - @OLDPOS
  WHEN @NEWPOS > @OLDPOS AND [Order] > @OLDPOS AND [Order] <= @NEWPOS THEN -1
  WHEN @NEWPOS < @OLDPOS AND [Order] < @OLDPOS AND [Order] >= @NEWPOS THEN 1
  ELSE 0
END

Here are some tests

Gaps and overlaps could be fixed by adding ROW_NUMBER() to the above.

Some moar tests

user_870150
Related