0 Replies - 458 Views - Last Post: 18 February 2016 - 01:44 AM

#1 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4223
  • View blog
  • Posts: 13,378
  • Joined: 08-June 10

Calculate Difference between two Rows in SQL

Posted 18 February 2016 - 01:44 AM

HI there,

this one picked my interest as I was reading through some related discussion. Basically it is the question how to calculate the difference of a value in subsequent rows.

example:
+----+-------+
| id | value |
+----+-------+
|  1 |     3 |
|  2 |     8 |
|  3 |     5 |
+----+-------+


for that finding the difference is trivial
SELECT
    c.value,
    c.value - p.value AS diff
FROM
    table AS c
INNER JOIN
    table AS p
    ON
        p.id = c.id - 1
;


obviously that only works if no row has been deleted. so my question is, assuming that you cannot rely on the sequence of the ids (i.e. idx+1 = idx + 1) how can you calculate the difference between adjacent rows in SQL which might not even be related by id, but for instance by a datetime?

(Im aware that you can do that quite easy in any programming language).

Dormi

Is This A Good Question/Topic? 0
  • +

Page 1 of 1