title: Comparing using CASE with USER VARIABLES
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>Conversed to this</p>
  
  <pre><code>ID    NEWID     CODE       COUNT   
  102   102       AB         9
  101   101       AB         8
  99    100       AB         7
  98    99        AB         6
  97    98        AB         5
  96    97        AB         0
  </code></pre>
  
  <p>Using</p>
  
  <pre><code>SELECT
  t.ID, t.CODE, t.COUNT,
  @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;
  </code></pre>
  
  <p><a href="http://sqlfiddle.com/#!2/e0b8b/36/0" rel="nofollow">http://sqlfiddle.com/#!2/e0b8b/36/0</a></p>
  
  <p>So</p>
  
  <pre><code>Step 1: 9 - 1 = 1
  Step 2: 8 - 7 = 1
  Step 3: 7 - 6 = 1
  Step 4: 6 - 5 = 1
  Step 5: 5 - 0 = 5
  </code></pre>
  
  <p>Now there could be a case where I have </p>
  
  <pre><code>ID    NEWID     CODE       COUNT   
  102   102       AB         4
  101   101       AB         2
  99    100       AB         1
  98    99        AB         0
  97    98        AB         7
  96    97        AB         0
  </code></pre>
  
  <p>Then I want to count</p>
  
  <pre><code>Step 1: 4 - 2 = 2
  Step 2: 2 - 1 = 1
  Step 3: 1 - 0 = 1
  Step 4: 0 - 7 = -7      //want  to discard this negative value
  Step 5: 5 - 0 = 7
  </code></pre>
  
  <p>Where I want to discard step 4 because this is negative.</p>
  
  <p>Now I use this code to discard the negative value</p>
  
  <pre><code>SELECT
  t.ID, t.CODE, t.COUNT,
  @PREVCOUNT,
  @PREVCOUNT - t.COUNT DIFFERENCE,
  CASE @PREVCOUNT - t.COUNT WHEN @PREVCOUNT - t.COUNT &gt;= 0 THEN 'equal or bigger' ELSE 'smaller' END,
  @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;
  </code></pre>
  
  <p>After executing this code I expect to see where the comparison made is bigger as or equal to 0, instead I get <em>very</em> different results.</p>
  
  <p>Below a link to see what I mean: </p>
  
  <p><a href="http://sqlfiddle.com/#!2/6be4a/1" rel="nofollow">http://sqlfiddle.com/#!2/6be4a/1</a></p>
  
  <p>I would really like to know what is going wrong here, and I would love to have a solution.</p>
  
  <p>Kind regards</p>