31225

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:

<strong>Move Up</strong>

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

<strong>Move Down</strong>

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

<strong>Move to top</strong>

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

<strong>Move to Bottom</strong>

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.

Answer1:

T-SQL's <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql" rel="nofollow">ROW_NUMBER()</a> 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:

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

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 <em>any time</em> we make a change to that order, we have to update <em>a lot</em> of the existing order values in the table.

For example:

<ul><li>The user modifies a record so its order changes from 5 to 2. We now have to shift <em>every record</em> from order 2 onwards to the end of the order.</li> </ul>

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.

Answer2:

When you have the ordering column normalized to values from 1 to <em>number of items</em> 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.

Answer3:

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.

Answer4:

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).

Recommend

  • Specifying field size of Map collection in grails DOM
  • Spark DataFrame equivalent to Pandas Dataframe `.iloc()` method?
  • What is #:: method
  • Escaping a LIKE pattern or regexp string in Postgres 8.4 inside a stored procedure
  • mapping between two ontologies
  • Yii2: Using Kartik Depdrop Widget?
  • CS1703: In Xamarin.Droid, should I use the .Net Standard windowsruntime.dll located in Mono.Framewor
  • Creating UDF with VSTO in Excel
  • How to set an entity field that does not exist on the table but does exists in the raw SQL as an ali
  • how to query for min or max inet/cidr with postgres
  • How to Add Polymorphic Comments to Feed?
  • Xaml, wpf image position and crop issue
  • Need code translation from VB to C#
  • Best practice to eliminate magic numbers within a member function
  • sweetalert2 inputoptions from file in select example
  • Getting different value with placeholder over CPU/GPU
  • Owin Authentication and claims in asp.net how to access user data
  • msbuild create itemgroup from property group
  • Eliminate partial duplicate rows from result set
  • ListItem.Attributes.Add not working
  • Reduction and collapse clauses in OMP have some confusing points
  • MongoDb aggregation
  • How to use remove-erase idiom for removing empty vectors in a vector?
  • Getting last autonumber in access
  • PHP - How to update data to MySQL when click a radio button
  • Sending data from AppleScript to FileMaker records
  • Join two tables and save into third-sql
  • How to handle AllServersUnavailable Exception
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • php design question - will a Helper help here?
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • KeystoneJS: Relationships in Admin UI not updating
  • AngularJs get employee from factory
  • WPF Applying a trigger on binding failure
  • Load html files in TinyMce
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize
  • How do I use LINQ to get all the Items that have a particular SubItem?