Reordering an ordered list

I have a following SQL table with data

ProductList 
id  order productname
79   1     name1
42   2     name2
67   3     somename
88   4     othername
99   5     XYZ
66   6     ABC

Display order is very volatile, it will change frequently, users will add or remove items and reorder the items.

How should i handle this situation without updating multiple开发者_StackOverflow records. Example: if user enters a new product between 1 and 2 order, i do not want to update the order of all the records beneath 2 and if someone switch order 3 to 4 i don't want to update every record under 3.

Use the 'orrible old trick made famous(?) by old BASIC coders - set your orders to be 100, 200, 300, 400 etc. and then you can pick an order 'inbetween' when you need to. This could get messy - and if you're anticipating a lot of reordering then I'd recommend that you have a scheduled task to 'reorder' the order values every now and then for the entire table.

A second answer to the same question:

Use a DOUBLE field for ordering and split the difference between the two values you want to insert between. In any standard business application, I doubt very, very much whether you'll ever approach the number of inserts beyond which you cannot resolve the sort order difference.

You have two options:

• Iterate over the rows, using an UPDATE statement with a function/etc to generate the updated order value
• Scorched Earth: You delete the existing records, and insert identical ones save the corrected order value

There's no SQL functionality to make this easier, and no real option that is simplistic.

You can use a string in place of a number of infinitely many digits. If you want to insert between "1" and "2" make it a "15".

You could instead use a linked list for ordering (with some special way to identify the head).

Insertions are one INSERT and one UPDATE. Deletions are one DELETE and one UPDATE. A move is three UPDATEs. (Use transactions to ensure the linked list doesn't break, of course.)

Add a DATETIME column called OrderDateTime. Use this column (in descending order) to resolve "ties" in ordering, and only update it when an ordering operation takes place.

For instance, in your example, assume all the rows have an OrderDateTime value from yesterday.

Now, to insert an item between 1 and 2, you'd set it's Order value to 2 and it's OrderDateTime value to now. When you SELECT * FROM ProductList ORDER BY Order ASC, OrderDateTime DESC the new number 2 item will sort before the existing one.

Similarly, to swap items 4 and 5, you'd update item 5 to have an order of 4 and an OrderDateTime of now. It would become a more recent 4 item and appear earlier.

You need to watch out, if you try to insert an item between two other items that already have the same Order value, that you split the OrderDateTime value difference.

In my case, I allow users to reorder the steps in their tour. I have a column called STEP that they can change and I created a column called STEP2. The STEP2 column is automatically updated to the same value as step. But when a user changes STEP, I order by the proposed change and the original order (STEP2) DESC. After the user updates STEP to the new value, I save to the database. Then I run a proc to resequence the steps.

The proc is:

DECLARE @t StackTable

INSERT INTO @t(Id)
SELECT Id
FROM TourStops
WHERE TourIdRef = @tourId
ORDER BY [Step], [Step2] DESC

UPDATE TourStops
SET [Step] = t.Position, [Step2] = t.Position
FROM TourStops s join @t t on s.Id = t.Id

StackTable is a UDT with 2 columns: Id and Position. Position is an IDENTITY column. Each time it is created it starts with 1 and increments by 1 for each row added.

Based on Larry Lustig's suggestion of using a double, I've come up with this. Running against a table with 10K rows, it runs pretty fast. Suggestions for improving efficiency are certainly welcome!

--- Note:
--- [Order] column is a value type of double
--- when inserting into MyTable, set [Order] to
--- MAX([Order])+10
CREATE PROCEDURE dbo.MoveUpLevel
(
    @MyTableID int
)
AS
BEGIN
SET NOCOUNT ON


DECLARE @LevelMe float
DECLARE @LevelMin float
DECLARE @LevelMax float
SELECT @LevelMe = [Order]
    FROM dbo.MyTable WITH (NOLOCK)
    WHERE MyTableID = @MyTableID
SELECT @LevelMin = MIN([Order])
      ,@LevelMax = MAX([Order])
    FROM dbo.MyTable WITH (NOLOCK)

--- Check if already at the top
IF @LevelMe = @LevelMin
    RETURN(0)

DECLARE @LevelAboveMe float
SELECT TOP 1 @LevelAboveMe = [Order]
    FROM dbo.MyTable WITH (NOLOCK)
    WHERE [Order] < @LevelMe
    ORDER BY [Order] DESC

DECLARE @LevelAboveMe2 float = 0
IF NOT @LevelAboveMe = @LevelMin
    SELECT TOP 1 @LevelAboveMe2 = [Order]
        FROM dbo.MyTable WITH (NOLOCK)
        WHERE [Order] < @LevelAboveMe
        ORDER BY [Order] DESC

-- calculate new level
SET @LevelMe = @LevelAboveMe2 + ((@LevelAboveMe - @LevelAboveMe2)/2)
-- store to DB
UPDATE dbo.MyTable
    SET [Order] = @LevelMe
    WHERE MyTableID = @MyTableID        

RETURN(0)
END
GO

CREATE PROCEDURE dbo.MoveDownLevel
(
     @MyTableID int
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @LevelMe float
DECLARE @LevelMin float
DECLARE @LevelMax float
SELECT @LevelMe = [Order]
    FROM dbo.MyTable WITH (NOLOCK)
    WHERE MyTableID = @MyTableID
SELECT @LevelMin = MIN([Order])
      ,@LevelMax = MAX([Order])
    FROM dbo.MyTable WITH (NOLOCK)

--- Check if already at the bottom
IF @LevelMe = @LevelMax
    RETURN(0)

DECLARE @LevelBelowMe float
SELECT TOP 1 @LevelBelowMe = [Order]
    FROM dbo.MyTable WITH (NOLOCK)
    WHERE [Order] > @LevelMe
    ORDER BY [Order] ASC

DECLARE @LevelBelowMe2 float = @LevelMax + 10
IF NOT @LevelBelowMe = @LevelMax
    SELECT TOP 1 @LevelBelowMe2 = [Order]
        FROM dbo.MyTable WITH (NOLOCK)
        WHERE [Order] > @LevelBelowMe
        ORDER BY [Order] ASC

-- calculate new level
SET @LevelMe = @LevelBelowMe + ((@LevelBelowMe2 - @LevelBelowMe)/2)

-- store to DB
UPDATE dbo.MyTable
    SET [Order] = @LevelMe
    WHERE MyTableID = @MyTableID        

RETURN(0)
END
GO