Menu
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)?
252
Here's the SQL query to compare each row with previous row. In the above query, we join sales table with itself using an INNER JOIN condition g2.id=g1.id + 1 that allows you to compare each row with its previous row. Please note, this condition depends on the fact that our id column has consecutive numbers.
In the blue text, you can see the calculation of the SQL delta between two rows. To calculate a difference, you need a pair of records; those two records are “the current record” and “the previous year's record”. You obtain this record using the LAG() window function.
One of the easiest ways, to compare this is using the lag function. The lag function will allow you to shift the rows downward so that you can view these rows as one observational row. Here is a simple example of how to use the lag function to shift rows downward.
SQL Server DIFFERENCE() Function The DIFFERENCE() function compares two SOUNDEX values, and returns an integer. The integer value indicates the match for the two SOUNDEX values, from 0 to 4. 0 indicates weak or no similarity between the SOUNDEX values.
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
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.
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
