title: Detecting negative change and acting upon this
body: <p>I have this table where I only want to look at AB</p>
  
  <pre><code>ID     CODE       COUNT   
  102    AB         9
  101    AB         8
  100    AC         23    //not important!!!!
  99     AB         7
  98     AB         6
  97     AB         5
  96     AB         0
  </code></pre>
  
  <p>And I then want to count the differences between the specific ID's which have CODE 'AB'</p>
  
  <p>So</p>
  
  <pre><code>Step 1: 9 - 8 = 1   // (corrected sample data. this line was wrong)
  Step 2: 8 - 7 = 1
  Step 3: 7 - 6 = 1
  Step 4: 6 - 5 = 1
  Step 5: 5 - 0 = 5
  </code></pre>
  
  <p>This I do by this query made by @bonCodigo</p>
  
  <pre><code>select ID, DIFFERENCE, 
  COUNT from (
      SELECT
      t.ID, t.CODE, t.COUNT,
      @PREVCOUNT,
      @PREVCOUNT - t.COUNT DIFFERENCE,
      @PREVCOUNT := t.COUNT  -- Updates for the next iteration, so it
                             -- must come last!
      FROM
      (SELECT ID, CODE, COUNT FROM some_table WHERE CODE = 'AB' ORDER BY ID DESC) t,
      (SELECT @PREVCOUNT := NULL) _uv
      group by t.id, t.code
      )x
  where x.difference &gt;= 0
  order by ID DESC;
  </code></pre>
  
  <p>As my new incoming data sometimes RESETS the count, it starts counting from 0 till whatever.</p>
  
  <p>So sometimes I get data in this order:</p>
  
  <pre><code>ID        COUNT
  1.        0
  2.        1
  3.        2
  4.        7
  5.        4     // which means the counter has reset to 0 and counted up to 4 again.
  6.        5
  </code></pre>
  
  <p>Now what my query is doing, it counts only the positive changes and takes this as a difference. </p>
  
  <p>So what it does:</p>
  
  <pre><code>Step 1: 1 - 0 = 1
  Step 2: 2 - 1 = 1
  Step 3: 7 - 2 = 5
  Step 4: 4 - 7 = -3 //discarded as this difference is smaller than 0
  Step 5: 5 - 4 = 1
  </code></pre>
  
  <p>So if I SUM() this I get <strong>8</strong>
  <a href="http://sqlfiddle.com/#!2/6924a/2" rel="nofollow">http://sqlfiddle.com/#!2/6924a/2</a></p>
  
  <p>While I want this code to count from 0 as soon as there is a negative difference</p>
  
  <p>So what I want:</p>
  
  <pre><code>Step 1: 1 - 0 = 1
  Step 2: 2 - 1 = 1
  Step 3: 7 - 2 = 5
  Step 4: 4 - 7 = -3  BUT MAKE IT 4 because the counter started from 0 again.
  Step 5: 5 - 4 = 1
  </code></pre>
  
  <p>So if I SUM() this I get <strong>12</strong></p>