In MySQL database sometimes we want to calculate difference between values in successive rows. Consider an example where the score of a cricket match after every over is stored in another row. The syntax to create such a table is given below:

CREATE TABLE tblCricket (

   id INT AUTO_INCREMENT PRIMARY KEY,

   player VARCHAR(20) NOT NULL,

   score int(11) NOT NULL,

   PRIMARY KEY (id)

);

Where,

  • The id is auto-increment column.
  • The player is the name of player
  • The score is the total runs after the number of overs in id.

When we want to get the score for each over, and can see the difference in the score between successive rows, we may use a INNER JOIN of one row to the next. The condition INNER JOIN ON t2.id = t1.id+1 does the comparison of current row and next. This is possible because there is no gap in the 'id column'. The following example presents exactly such approach:

SELECT t1.player AS player1, t1.score AS score1,

t2.score AS score2, (score2- score1)  AS runsPerOver

FROM tblCricket AS t1 INNER JOIN tblCricket AS t2

ON t2.id = t1.id+1;

NOTE: If we have to do the comparison to a table which does not have sequential numbers in a column, we have to create additional column which maintains the sequence of numbers.