title: Update table by Preference and reorder table accordingly
body: <p>I have this app when you drag and drop rows in a list it reorders and SETS that row by preference number, 1-5 for example, 1 being most priority. So if I have 5 records in the list, I can then drag each row and when dropped it will reorder the list by preference.  I can move row 5 to row 1, or row 2 to row 3, etc...  This will update the preference number in the SQL table according where you drop.</p>
  <p>This app is in real-time.  When new rows are added to the table automatically, they have an initial preference of &quot;0&quot;.  This query will add the next number preference to the record, so if I have rows with preferences of 1-5, a new record comes in, then it's assigned a preference of 6:</p>
  <pre><code>with CTE as (
      select Id, Preference, cp.maxpref, row_number() over(order by Id) rn
      from [RadioQDB].[dbo].[Rad5]
      cross apply (
          select max(preference) maxpref
          from [RadioQDB].[dbo].[Rad5] p
      ) cp
      where preference = 0
  )
  update cte
  set preference = maxpref + rn
  where preference = 0
  </code></pre>
  <p>The issue I am having now is if a record is removed from the list during an update (not user drag and drop), let's say you have 1,2,3,4,5 records in the list.  If during the table update, a record is removed automatically, let's say #2, then you end up with preferences 1,3,4,5.  How can I move up everything and reorder the table accordingly by preference?
  1 stays the same, 3 moves to 2, 4 moves to 3 and so forth.</p>
  <p>Thank you.</p>