title: MySQL calculate only the row-differences for rows in the same group
body: <p>I have the following table (call it trans):</p>
  
  <pre><code>issue_id:   state_one:  state_two:  timer:      
  1           A           B           1
  1           B           C           3
  
  2           A           B           2
  2           B           C           4
  2           C           D           7
  </code></pre>
  
  <p>I'd like the get the difference in 'timer' between consecutive rows, but only those with the same issue_id. </p>
  
  <p>Expected result:</p>
  
  <pre><code>issue_id:   state_one:  state_two:  timer:    time_diff:                 
  1           B           C           3         2
  
  2           B           C           4         2
  2           C           D           7         3
  </code></pre>
  
  <p>When taking the time difference between two rows, I'd like the result displayed next to the later row.</p>
  
  <p>If we only had one, time-ordered issue in the table, the following code works fine:</p>
  
  <pre><code>select 
      X.issue_id, 
      X.timer as X_timer,  
      Y.timer as Y_timer, 
      (X.timer - Y.timer) as time_diff
  from trans X
  cross join trans Y
  where Y.timer in (
      select 
          max(Z.timer)
      from trans Z
      where Z.timer &lt; X.timer);
  </code></pre>
  
  <p>I want to generalize this approach to handle MANY issues with time-ordered state changes.</p>
  
  <p>My idea was to add the following condition, but it only works if consecutive events belong to the same issue (not the case in the real world):</p>
  
  <pre><code>... where Z.timer &lt; X.timer)
  and X.issue_id = Y.issueid;
  </code></pre>
  
  <p>Question: In MySQL, can I do this iteratively (i.e. calculate differences for issue_id=1, then for issue_id=2, and so on)? A function or subquery?</p>
  
  <p>Other strategies? Constraint: I have read-only privileges. I truly appreciate the help!</p>
  
  <p>EDIT: I added expected output, added a row to my example table, and clarified.</p>