Subtracting one row of data from another in SQL

Question

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.

So here is the table:

CREATE TABLE foo (
  id,
  length
)

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)

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.

+------+------------------------+
| id   |length |  difference  |
+------+------------------------+
|    1 | 1090  |  202         |
|    2 |  888  |  343         |
|    3 |  545  |  111         |
|    4 |  434  |  389         |
|    5 |   45  |   45         |

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.

This is what I've tried:

SELECT id, f.length, f2.length, (f.length - f2.length) AS difference
FROM foo f, foo f2

Thank you for the assist.

Answer 1

I had this problem and it was interesting to look at your solutions. I find it strange that such a normal-life problem is so complicated in SQL. As I need the values in a report only, I chose a completely different solution. I'm running Ruby on Rails as the front end of my sqlite3 database, and just did the subtraction in the view like this:

In your ruby controller, there is a object variable @foo that holds the rows returned by your query.

In the view, just do

<table border=1>
  <tr>
    <th>id</th>
    <th>length</th>
    <th>difference</th>
  </tr>

<% for i in [e-mail]-1 do %>
  <tr>
    <td><%=h @foo[i].id %></td>
    <td><%=h @foo[i].length %></td>
    <td><% if (i==@foo.length-1) then %>
        <%=  @foo[i].length %>
      <% else %>
        <%= @foo[i+1].length.to_i - @foo[i].length.to_i %>
      <% end %>
    </td>
  </tr>
<% end %>
</table>

Seems to be more robust than the SQL solutions.