title: Alternative to bubble sort in MySQL
body: <p><br/>
  the problem lies most likely in fact, I am not asking google(or stackoverflow) the right question, but since this simple thing is driving me nuts. Sorry for long post, but want to make situation clear.</p>
  
  <p>I have a mysql table of records
  ID, person_name, priority</p>
  
  <p>ID and person_name are quite selfexplanatory, the priority is used to sort the records descending order - highest = top priority.</p>
  
  <p>I have following records, for simplicity, IDs correspond the sorting order:</p>
  
  <pre><code>1, "John A", 3
  2, "John B", 2
  3, "John C", 1
  4, "John D", 0
  </code></pre>
  
  <p>This worked fine until I imported a record from a export from other machine, suddenly there are two records with same priority.</p>
  
  <pre><code>1, "John A", 3
  2, "John B", 2
  3, "John C", 1
  5, "John E", 1
  4, "John D", 0
  </code></pre>
  
  <p>When the records are now ordered by priority DESC, name ASC, the ID3 and ID5 are sorted alphabetically which does not correspond the situation. If user wants to shift priority of ID5 to be HIGHER than ID2, I can't just increment the priority, since it would match ID2 and ID5 would appear BELOW ID2 due to alpha-order.</p>
  
  <p>Question: Is there a way to do this more EFFECTIVELY than this, or just completely different approach:</p>
  
  <ol>
  <li>NEAREST_PRIORITY = Determine priority of nearest record with higher priority</li>
  <li>Increase priority of all records with priority > NEAREST_PRIORITY by 1</li>
  <li>Set current record priority = NEAREST_PRIORITY+1</li>
  </ol>
  
  <p>This will actually not work quite well if there are several records with NEAREST_PRIORITY...</p>
  
  <p>thanks for reading long post and any ideas.</p>