title: Using Real numbers for explicit sorting in sql database
body: <p>i'm facing a recurring problem. I've to let a user reorder some list that is stored in a database. </p>
  
  <p>The fist straightforward approach i can think is to have a "position" column with the ordering saved as a integer. p.e.</p>
  
  <pre><code>Data, Order
  A     1
  B     2
  C     3
  D     4
  </code></pre>
  
  <p>Problem here is that if i have to insert FOO in position 2, now my table become</p>
  
  <pre><code>Data, Order
  A     1
  FOO   2
  B     3
  C     4
  D     5
  </code></pre>
  
  <p>So to insert a new line, i have to do one CREATE and three UPDATE on a table of five elements.</p>
  
  <p>So my new idea is using Real numbers instead of integers, my new table become</p>
  
  <pre><code>Data, Order
  A     1.0
  B     2.0
  C     3.0
  D     4.0
  </code></pre>
  
  <p>If i want to insert a element FOO after A, this become</p>
  
  <pre><code>Data, Order
  A     1.0
  FOO   1.5
  B     2.0
  C     3.0
  D     4.0
  </code></pre>
  
  <p>With only one SQL query executed.</p>
  
  <p>This would work fine with theoretical Real Numbers, but floating point numbers have a limited precision and i wondering how feasible this is and whether and how can i optimize it to avoid exceeding double precision with a reasonable number of modifications</p>
  
  <p>edit:</p>
  
  <p>this is how i implemented it now in python</p>
  
  <pre><code>@classmethod
  def get_middle_priority(cls, p, n):
      p = Decimal(str(p))
      n = Decimal(str(n))
      m = p + ((n - p)/2)
  
      i = 0
      while True:
          m1 = round(m, i)
          if m1 &gt; p and m1 &lt; n:
              return m1
          else:
              i += 1
  
  @classmethod
  def create(cls, data, user):
      prev = data.get('prev')
  
      if prev is None or len(prev)&lt;1:
          first = cls.list().first()
  
          if first is None:
              priority = 1.0
          else:
              priority = first.priority - 1.0
      else:
          prev = cls.list().filter(Rotator.codice==prev).first()
          next = cls.list().filter(Rotator.priority&gt;prev.priority).first()
  
          if next is None:
              priority = prev.priority + 1.0
          else:
              priority = cls.get_middle_priority(prev.priority, next.priority)
  
      r = cls(data.get('codice'),
          priority)
  
      DBSession.add(r)
  
      return r
  </code></pre>