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

Back Home

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