# Calculate Difference between two Rows in SQL

Page 1 of 1

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

### #1 Dormilich

• 痛覚残留

Reputation: 4223
• 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?

(I’m aware that you can do that quite easy in any programming language).

Dormi

Is This A Good Question/Topic? 0

Page 1 of 1

 .related ul { list-style-type: circle; font-size: 12px; font-weight: bold; } .related li { margin-bottom: 5px; background-position: left 7px !important; margin-left: -35px; } .related h2 { font-size: 18px; font-weight: bold; } .related a { color: blue; }