Finding the difference in rows in query using sqlite

Question

After a few hours of searching, I'm not getting anything that works with Sqlite. It's driving me nuts.

I have an sqlite table that contains prices for various products. It's a snapshot table, so it contains the prices on 5 minute intervals. I would like to write a query that would return the difference in price from one row to the next on each item.

The columns are id (auto inc), record_id (id of the product), price (price at that point in time), time (just seconds since epoch)

I'm trying to return a 'difference' column that contains a value of difference between intervals.

Given the following

id      record_id       price   time
1       apple001        36.00   ...
67      apple001        37.87   ...
765     apple001        45.82   ...
892     apple001        26.76   ...

I'd like it to return
id      record_id       price   time    difference
1       apple001        36.00   ...     0
67      apple001        37.87   ...     1.87
765     apple001        45.82   ...     7.95
892     apple001        26.76   ...     -19.06

Is it possible with sqlite?

Secondly, should it be possible - is there a way to limit it to the last 5 or so records?

I'd appreciate any help, thanks.

---

Just wanted to add a few things. I've found ways to do so in other databases, but I'm using xulrunner, thus sqlite. Which is why I'm working with it instead.

The secondary question may need clarifying, I'm looking to order by the time and take and analyze the last 5 records. It's an issue I can tackle seperately if need be.

Here's a mysql solution, kind of. It's the approach I'm heading towards, but the deal breaker is "If the table contains a sequence column but there are gaps, renumber it. If the table contains no such column, add one". By design this scenario has gaps as there is many records updated at once and won't be in order.

Answer 1

I do not know if there are some limitations in SQLite, but you can try the following statements that are working in Sql Server.

If the time difference is constant (you state that it is 5 minutes), you can write:

SELECT A.id, A.record_id, A.price, A.time, ISNULL(A.price - B.price, 0) AS difference
FROM Table1 as A 
    LEFT OUTER JOIN Table1 B ON A.record_id = B.record_id AND A.time - B.time = 5

otherwise

SELECT A.id, A.record_id, A.price, A.time, ISNULL(A.price - B.price, 0) AS difference
FROM Table1 as A 
    LEFT OUTER JOIN Table1 B ON B.record_id = A.record_id 
         AND B.time = (SELECT MAX(time) FROM Table1 C WHERE C.time < A.time AND C.record_id = A.record_id)

A statement without joins is the following

SELECT id, record_id, price, time,
    (SELECT A.price - B.price
     FROM Table1 as B
     WHERE B.record_id = A.record_id AND
      B.time = (SELECT MAX(time) FROM Table1 C WHERE C.time < A.time AND C.record_id = A.record_id)) AS difference
FROM Table1 as A

I hope that one of them will help you.