Manage records ordering

Question

I want the user of my application to change the order of records using a filed called Order. I generated some scripts in order to change the order of the records but I think there should be a more optimized way to do it.

My test table is called MyTable. The ID of the table is called ID and the order field is called Order.

My SQL commands are the following:

Move Up

Declare @ID int = 3;
Declare @Order int = (SELECT [Order] FROM MyTable WHERE ID = @ID);
Declare @PreviousID int = (SELECT MAX(ID) FROM MyTable WHERE [Order] < @Order);
Update MyTable SET [Order] = @Order - 1 WHERE ID = @ID;
Update MyTable SET [Order] = @Order WHERE ID = @PreviousID

Move Down

Declare @ID int = 3;
Declare @Order int = (SELECT [Order] FROM MyTable WHERE ID = @ID);
Declare @NextID int = (SELECT MIN(ID) FROM MyTable WHERE [Order] > @Order);
Update MyTable SET [Order] = @Order + 1 WHERE ID = @ID;
Update MyTable SET [Order] = @Order WHERE ID = @NextID

Move to top

Declare @ID int = 3;
Declare @MinimumOrder int = (SELECT Min([Order]) FROM MyTable);
Update MyTable SET [Order] = @MinimumOrder - 1 WHERE ID = @ID;

Move to Bottom

Declare @ID int = 3;
Declare @MaximumOrder int = (SELECT Max([Order]) FROM MyTable);
Update MyTable SET [Order] = @MaximumOrder + 1 WHERE ID = @ID;

These SQL commands work without problem. It also can have negative numbers for the Order field.

I also would like to generate one more SQL script which will update the Order filed so that it will update the Order filed so that the Order will start from 1 and increase it's value by 1. This is useful because sometimes we may delete records or my scripts may produce negative order numbers. If for example you try to move up the record with Order = 1, it will have as a result the Order to take the value 0 and if you do it again it will take value -1, etc.

Answer 1

When you have the ordering column normalized to values from 1 to number of items you can use code like the examples below to maintain the order. The general technique is to select all of the affected rows in the where clause of an update statement and use a case expression to update the ordering column appropriately.

-- Sample data.
declare @Samples as Table ( SampleId Int Identity, DisplayOrder Int, Name VarChar(20) );
insert into @Samples ( DisplayOrder, Name ) values
  ( 1, 'Chutney' ), ( 2, 'Marshmallows' ), ( 3, 'Carrots' ), ( 4, 'Cheddar' );

select * from @Samples order by DisplayOrder;

-- Swap display orders so that the target row is moved to the target display position.
declare @TargetId as Int = 2;
declare @TargetDisplayOrder as Int = 1;

update @Samples
  set DisplayOrder = case
    when SampleId = @TargetId then @TargetDisplayOrder
    else ( select DisplayOrder from @Samples where SampleId = @TargetId ) end
  where SampleId in ( @TargetId,
    ( select SampleId from @Samples where DisplayOrder = @TargetDisplayOrder ) );

select * from @Samples order by DisplayOrder;

-- Move the target row up one position in the display order.
set @TargetId = 3;

update @Samples
  set DisplayOrder = case
    when SampleId = @TargetId then DisplayOrder - 1
    else DisplayOrder + 1 end
  where SampleId in ( @TargetId,
    ( select SampleId from @Samples where DisplayOrder =
      ( select DisplayOrder from @Samples where SampleId = @TargetId ) - 1 ) );

select * from @Samples order by DisplayOrder;

-- Move the target row down one position in the display order.
set @TargetId = 2;

update @Samples
  set DisplayOrder = case
    when SampleId = @TargetId then DisplayOrder + 1
    else DisplayOrder - 1 end
  where SampleId in ( @TargetId,
    ( select SampleId from @Samples where DisplayOrder =
      ( select DisplayOrder from @Samples where SampleId = @TargetId ) + 1 ) );

select * from @Samples order by DisplayOrder;

-- Move the target row up to the top in the display order.
set @TargetId = 1;

update @Samples
  set DisplayOrder = case
    when SampleId = @TargetId then 1
    else DisplayOrder + 1 end
  where DisplayOrder <= ( select DisplayOrder from @Samples where SampleId = @TargetId );

select * from @Samples order by DisplayOrder;

-- Move the target row down to the bottom in the display order.
set @TargetId = 3;

update @Samples
  set DisplayOrder = case
    when SampleId = @TargetId then ( select Max( DisplayOrder ) from @Samples )
    else DisplayOrder - 1 end
  where DisplayOrder >= ( select DisplayOrder from @Samples where SampleId = @TargetId );

select * from @Samples order by DisplayOrder;

Note that if you use multiple statements to perform work, e.g. insert a new row and then update to move it to the desired order, you need to wrap the statements in a transaction (with a suitable isolation level) to prevent multiple users from corrupting the data.

Answer 2

I do not know if this is the best way to do it but I managed to do it using a cursor.

DECLARE @i int = 1;
DECLARE @ID int

DECLARE db_cursor CURSOR FOR  
SELECT ID FROM MyTable ORDER BY [Order]

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @ID

WHILE @@FETCH_STATUS = 0   
BEGIN   
    update MyTable set [Order] = @i WHERE ID = @ID;
    SET @i = @i + 1;
       FETCH NEXT FROM db_cursor INTO @ID   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Please inform me in case there is a better way to do it.

Answer 3

T-SQL's ROW_NUMBER() operation might be useful to you in terms of consolidating order values.

This is an example of how you can use this functionality to consolidate your order values without affecting the ranking of items within that order:

/* declare placeholder and populate with test values */
declare @MyTable table (ID bigint identity(1,1), [Order] bigint)

insert into @MyTable ([Order]) 
VALUES
(1),
(3),
(2),
(5),
(-4),
(13),
(0)

/* Look at values we've just inserted */
select * from @MyTable order by [Order]

/* Show how ROW_NUMBER() can apply a consolidated ranking based on our existing order */
select *, ROW_NUMBER() over (order by [Order] asc) as sort from @MyTable

/* Apply that consolidated ranking to update the order values */
update @MyTable
set [Order] = consolidated.sort
from 
(
select ID as refID, ROW_NUMBER() over (order by [Order] asc) as sort from @MyTable
) consolidated
where consolidated.refID = ID

/* Final display of updated table */
select * from @MyTable order by [Order]

Ideally, though, it's worth taking some time to manage and keep clean the data from the start.

Part of this is database structure and normalization, looking at things like:

• Can a record exist and not be ordered?
• If there is more than one user, do they all use the same order, or would each user have a separate order ranking?
• Is it possible a user might have more than one ordering that they want to use and switch between?

If any of those are true, you might want to break the ordering out into a separate table for data integrity purposes (and because of transaction locks and things, it might be worthwhile to do even if you don't)

Database design aside, it's also worthwhile to look at how you handle the data operations for modifying record order.

If we're using a table with N records, and these records have a densely-packed order (as per what the query above demonstrates, where the order values are 1,2,3,4,5,etc.), then any time we make a change to that order, we have to update a lot of the existing order values in the table.

For example:

• The user modifies a record so its order changes from 5 to 2. We now have to shift every record from order 2 onwards to the end of the order.

One way to compensate for this is to calculate ranking using offsets - rather than ordering by 1,2,3... instead use larger values like 10,20,30.... This lets you handle the user's ordering changes without a lot of immediate database load (move something up to order 2? Slot it in at position 15, between 10 and 20), and you can then optimize the ordering later.

Answer 4

Below will not get the previous item in the [Order] (unless by luck).

Declare @PreviousID int = (SELECT MAX(ID) FROM MyTable WHERE [Order] < @Order);

Same problem with MIN(ID).