Query to find the difference between consecutive rows in mysql

Question

Since I'm new to MySQL, this question might be silly. How to find the difference between consecutive lines? Example:
The table (tableName = 'Abc') contains one row as follows:

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

Here I want to get an output like,

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

How to find a difference like this without any index (primary or automatic_increment)?

Answer 1

Self-join is one way to compare consecutive lines:

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

Answer 2

Use user-defined variables:

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

You will 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 IF is the only way to find how to set diff and update the @prevValue variable.

Answer 3

It would be better to do this outside the database by tracking the previous row. Here is 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 this in db for some reason, then it is probably best to create a stored procedure that will basically do the same thing: create an instance of a variable with a value of 0, report the difference for each row and this variable, then set variable to string value.

Edited to add order by clause as specified by John Pick