How To Compare Successive Rows Within The Same Table in MySQL: Examples

Examples
Sample Database

#1

CREATE TABLE inventory(
  id INT AUTO_INCREMENT PRIMARY KEY,
  counted_date date NOT NULL,
  item_no VARCHAR(20) NOT NULL,
  qty int(11) NOT NULL
);

#2

INSERT INTO inventory(counted_date,item_no,qty)
VALUES ('2014-10-01','A',20),
	   ('2014-10-01','A',30),
	   ('2014-10-01','A',45),
	   ('2014-10-01','A',80),
	   ('2014-10-01','A',100);

#3

SELECT 
    g1.item_no,
    g1.counted_date from_date,
    g2.counted_date to_date,
    (g2.qty - g1.qty) AS receipt_qty
FROM
    inventory g1
        INNER JOIN
    inventory g2 ON g2.id = g1.id + 1
WHERE
    g1.item_no = 'A';

Table List

customers

SELECT * FROM customers;

employees

SELECT * FROM employees;

offices

SELECT * FROM offices;

orderdetails

SELECT * FROM orderdetails;

orders

SELECT * FROM orders;

payments

SELECT * FROM payments;

productlines

SELECT * FROM productlines;

products

SELECT * FROM products;

Result