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 separately 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.