title: Subtracting one row of data from another in SQL
body: <p>I've been stumped with some SQL where I've got several rows of data, and I want to subtract a row from the previous row and have it repeat all the way down.</p>
  
  <p>So here is the table:</p>
  
  <pre>CREATE TABLE foo (
    id,
    length
  )</pre>
  
  <pre>INSERT INTO foo (id,length) VALUES(1,1090)
  INSERT INTO foo (id,length) VALUES(2,888)
  INSERT INTO foo (id,length) VALUES(3,545)
  INSERT INTO foo (id,length) VALUES(4,434)
  INSERT INTO foo (id,length) VALUES(5,45)</pre>
  
  <p>I want the results to show a third column called difference which is one row subtracting from the one below with the final row subtracting from zero.</p>
  
  <pre>
  +------+------------------------+
  | id   |length |  difference  |
  +------+------------------------+
  |    1 | 1090  |  202         |
  |    2 |  888  |  343         |
  |    3 |  545  |  111         |
  |    4 |  434  |  389         |
  |    5 |   45  |   45         |</pre>
  
  <p>I've tried a self join but I'm not exactly sure how to limit the results instead of having it cycle through itself.  I can't depend that the id value will be sequential for a given result set so I'm not using that value.  I could extend the schema to include some kind of sequential value.</p>
  
  <p>This is what I've tried:</p>
  
  <pre>SELECT id, f.length, f2.length, (f.length - f2.length) AS difference
  FROM foo f, foo f2</pre>
  
  <p>Thank you for the assist.</p>