Rackham Le Rouge Rackham Le Rouge - 2 months ago 7
SQL Question

SQL difference between two rows having the same id

I would like to know how to take the difference in value (Val) for two rows having the same ID and return that from a SQL query. The difficulty here is that I was the difference between the second row and the first one, and each ID only has two rows.

I am using SQLite 3.8.6.

Here is what my table looks like:

ID Val TimeStamp
+---+----+---------------------+
| 2 | 7 | 2015-05-09 12:54:39 |
| 2 | 2 | 2015-05-09 14:48:30 |
| 3 | 16 | 2015-05-09 13:19:57 |
| 3 | 20 | 2015-05-09 15:01:09 |
+---+----+---------------------+


I want to end up with:

ID Val
+---+----+
| 2 | -5|
| 3 | 4 |
+---+----+

Answer
select
   t1.id,t2.val-t1.val AS [Val]
from table t1
inner join table t2 ON t1.ID=t2.ID
   AND t1.TimeStamp<t2.Timestamp