Query to find the difference between successive rows in Mysql

mysql database-table
Deepak Mani picture Deepak Mani · Mar 14, 2012 · Viewed 10.8k times · Source

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)?

Answer

John Pick picture John Pick · Mar 14, 2012

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

Related questions

How do I specify unique constraint for multiple columns in MySQL?

I have a table: table votes ( id, user, email, address, primary key(id), ); Now I want to make the columns user, email, address unique (together). How do I do this in MySql? Of course the example is just... an example. …

Read More
MySQL > Table doesn't exist. But it does (or it should)

I changed the datadir of a MySQL installation and all the bases moved correctly except for one. I can connect and USE the database. SHOW TABLES also returns me all the tables correctly, and the files of each table exists …

Read More
Import CSV to mysql table

What is the best/fastest way to upload a csv file into a mysql table? I would like for the first row of data be used as the column names. Found this: How to import CSV file to MySQL table …

Read More