CODEWITHSUNDEEP
sqlt-sql
mrjamiebowman
mrjamiebowman

Reputation: 115

SQL: Reordering List Order

I want to loop through a table row.. reorder my "Form Options" by setting the OrderID to @NUMCOUNT...

I want to be able to rearrange the order of my List. The reason I need to do this is because if I delete a Form Option then it will be out of order.. i.e (1, 2, 3, 5).. I will want (1, 2, 3, 4).. etc..

Example table:

ID OrderID FormOption

1 1 Name

2 3 Address 2

3 2 Address 1

DECLARE @NUMCOUNT int
SET @NUMCOUNT = 0
WHILE (SELECT Count(OrderID) FROM FormOptions WHERE ProductID=1) > @NUMCOUNT
BEGIN
   SET @NUMCOUNT = @NUMCOUNT + 1
   PRINT 'The count is ' + CAST(@NUMCOUNT as char)
   UPDATE FormOptions SET OrderID = @NUMCOUNT WHERE ID=????
END

Upvotes: 1

Views: 1187

Answers (3)

HABO
HABO

Reputation: 15816

I'm guessing that you are working on something that needs to allow deleting from an ordered list and inserting into an ordered list at a specific position. Deleting doesn't require making the order dense, but it can be done:

delete from FormOptions
  where OrderId = @SelectedFormOption
update FormOptions
  set OrderId = OrderId - 1
  where OrderId > @SelectedFormOption

You may want to wrap that in a transaction. (Make sure that you understand transactions as this is rather important.)

Inserting is similar:

update FormOptions
  set OrderId = OrderId + 1
  where OrderId >= @TargetOrderId
insert into FormOptions
  ( OrderId, ... ) values ( @TargetOrderId, ... )

Swapping order positions may be done atomically in a single update:

 update FormOptions
   set OrderId = case when OrderId = @TargetA then @TargetB else @TargetB end
   where OrderId in ( @TargetA, @TargetB )

A similar update can be written to move a single form option up or down one position in the order.

Upvotes: 3

Rob S
Rob S

Reputation: 718

I would advise against not using counting for ordering. In the past I've had multiple columns for ordering based on whatever the user decided.

One option to quickly fix the issue is to give an orderID > Max(orderID) That way you ensure each new item gets an orderID > than the highest value, regardless of number of items in the list.

You can then have a column just for sorting purposes and allow users to set the order of things.

Sometimes it's easier (and better) to think of how you can change what you're doing, rather than code to do what your thinking :)

Upvotes: 0

T I
T I

Reputation: 9933

I would suggest leaving OrderId alone as @OldProgrammer has said they will still be in order, plus (unless i'm missing something) it would seem to add unnecessary overhead.

If you really want to have continuous numbers, presumably for presentation purposes, then either push that to the presentation tier, or create a view which would do something like

SELECT 
    ROW_NUMBER() AS OrderNumber
    , OtherColumn
FROM FormOptions 
ORDER BY OrderID

Upvotes: 0

Related Questions