title: How to reorder a sql table
body: <p>I have a table that looks like this:</p>
  
  <pre><code>RecNo | TaskDesc | SeqNumber
  1     |  test    | 1
  2     |  test2   | 2
  3     |  test3   | 3
  </code></pre>
  
  <p>These items can be moved around using a reorder-able grid so test 3 can become SeqNumber 1 and test can become SeqNumber2. Im try to find a way to pass the new position and record number of taskDesc to a stored procedure and have it set the new position and adjust the other items. I have been somewhat successful but sometimes the numbers don't get updated. Here is a sample of what I have been trying</p>
  
  <pre><code>Update tblTasks Set SeqNum = SeqNum + 1
  Where SeqNum &gt;= @newPosition 
  AND SeqNum &lt; (Select SeqNum From tblTasks WHERE RecNo = @UpdatedRecNo)    
  
  Update tblTasks Set SeqNum = @newPosition 
  Where RecNo = @UpdatedRecNo
  </code></pre>
  
  <p>The problem is depending on how you move the rows the numbers can end up like this:</p>
  
  <pre><code>RecNo | TaskDesc | SeqNumber
      1     |  test    | 3
      2     |  test2   | 2
      3     |  test3   | 2
  </code></pre>
  
  <p>or</p>
  
  <pre><code>RecNo | TaskDesc | SeqNumber
      1     |  test    | 4
      2     |  test2   | 5
      3     |  test3   | 2
  </code></pre>
  
  <p>The don't stay within the bounds of 1,2,3 and do not remain unique. The SeqNum is used to represent urgency 1 being the highest.</p>