title: Manage records ordering
body: <p>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. </p>
  
  <p>My test table is called MyTable. The ID of the table is called ID and the order field is called Order.</p>
  
  <p>My SQL commands are the following:</p>
  
  <p><strong>Move Up</strong></p>
  
  <pre><code>Declare @ID int = 3;
  Declare @Order int = (SELECT [Order] FROM MyTable WHERE ID = @ID);
  Declare @PreviousID int = (SELECT MAX(ID) FROM MyTable WHERE [Order] &lt; @Order);
  Update MyTable SET [Order] = @Order - 1 WHERE ID = @ID;
  Update MyTable SET [Order] = @Order WHERE ID = @PreviousID
  </code></pre>
  
  <p><strong>Move Down</strong></p>
  
  <pre><code>Declare @ID int = 3;
  Declare @Order int = (SELECT [Order] FROM MyTable WHERE ID = @ID);
  Declare @NextID int = (SELECT MIN(ID) FROM MyTable WHERE [Order] &gt; @Order);
  Update MyTable SET [Order] = @Order + 1 WHERE ID = @ID;
  Update MyTable SET [Order] = @Order WHERE ID = @NextID
  </code></pre>
  
  <p><strong>Move to top</strong></p>
  
  <pre><code>Declare @ID int = 3;
  Declare @MinimumOrder int = (SELECT Min([Order]) FROM MyTable);
  Update MyTable SET [Order] = @MinimumOrder - 1 WHERE ID = @ID;
  </code></pre>
  
  <p><strong>Move to Bottom</strong></p>
  
  <pre><code>Declare @ID int = 3;
  Declare @MaximumOrder int = (SELECT Max([Order]) FROM MyTable);
  Update MyTable SET [Order] = @MaximumOrder + 1 WHERE ID = @ID;
  </code></pre>
  
  <p>These SQL commands work without problem. It also can have negative numbers for the Order field. </p>
  
  <p>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.</p>