Query to find the difference between successive rows in Mysql

Question

As I am new to MySQL, this question may be silly. How can I find the difference between successive rows?
Example:
A table (tableName='Abc') contains a single row as follows,

|DATA|
|10  |
|20  |
|30  |
|40  |
|50  |

Here I want get the output as,

|Dif|
|10 |
|10 |
|10 |
|10 |

How to find the difference like this without any index (primary or Auto_increment)?

That's a column, not a row, and it's only coincidentally ordered. Unless you use an ORDER BY clause in a SQL statement, MySQL can return the data in any order. — John Pick, Mar 14 '12 at 06:14

score 9 · Answer 1 · answered Mar 14 '12 at 06:20

9

A self-join is one way to compare consecutive rows:

SELECT
    MIN(T2.DATA - T1.DATA) AS Dif
FROM
    Abc T1 INNER JOIN Abc T2 on T1.DATA < T2.DATA
ORDER BY
    T1.DATA

answered Mar 14 '12 at 06:20

John Pick

5,562
31
31

1

`on T1.DATA < T2.DATA` wouldn't cause all rows below to produce a x*y rows ? I mean it wouldn't join 50&10,50&20,.. etc ? – prajeesh kumar Mar 14 '12 at 07:39
1

@prajeeshkumar The join would be triangular `(x*x+x)/2` if it weren't for `MIN()`, which ensures the rows match 1:1, except the last row, which doesn't match anything. The result is `x-1` rows. – John Pick Mar 14 '12 at 20:07
2

This seems to work only for monotonically increasing datasets, no? Did I miss something? When I run it, I get either a singleton (when there are no differences between values) or incorrect time differencing (when the dataset is non-monotonic). – DrFriedParts Apr 26 '13 at 03:36
1

Using group by instead of order by gives me the results I'm seeking – sk8asd123 Nov 18 '13 at 16:25

score 7 · Answer 2 · answered Aug 09 '13 at 18:12

Use user-defined variables:

SET @prevValue:=0;
SELECT value-@prevValue AS diff,@prevValue:=value FROM t;

You'll have to discard the first row for your case.

If the table contains both value and diff fields, you can set the diff field with:

SET @prevValue:=0;
UPDATE t SET diff=IF((@d:=value-@prevValue) AND (@prevValue:=value),@d,@d);

Using the IF is the only way I could find to both set diff and update the @prevValue variable.

score 4 · Answer 3 · answered Mar 14 '12 at 06:17

It would be best to do this outside of the DB by keeping track of the previous row. Here's some code (hopefully my php is not too rusty):

<?php

$prevNum = 0;

$db = mysql_connect(...); // fill in connection string

mysql_select_db("my_db", $db);

$result = mysql_query("select DATA from Abc order by DATA");

while ($row = mysql_fetch_array($result)) {

    $diff = $row[0] - $prevNum;

    echo "$diff\n";

    $prevNum = $row[0];

}

?>

If you need to do it within the db for some reason then it would probably be best to create a stored procedure which will basically do the same thing: instantiate a variable with value 0, report the difference for each row and that variable, then set the variable to the row value.

Edited to add an order by clause as noted by John Pick

Query to find the difference between successive rows in Mysql

3 Answers3