How To Compare Successive Rows Within The Same Table in MySQL
Summary: in this tutorial, we will show you how to compare successive rows within the same table using the self-join technique.
Suppose you have a table called inventory with the structure defined by the CREATE TABLE statement as follows:
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
);Code language: SQL (Structured Query Language) (SQL)
In the inventory table:
- The
idis an auto-increment column. - The
counted_dateis the counted date. - The
item_nois the item code posted to inventory? - The
qtyis the accumulated on-hand quantity in inventory.
The following is the sample data of the inventory table:
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);Code language: SQL (Structured Query Language) (sql)
If you want to know how many items received per day for each item, you need to compare the on-hand quantity of a particular day with its previous day.
In other words, in the inventory table, you need to compare a row with its successive row to find the difference.
In MySQL, you can use self-join technique to compare successive rows as the following query:
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';Code language: SQL (Structured Query Language) (sql)
The condition in the INNER JOIN clause g2.id = g1.id + 1 allows you to compare the current row with the next row in the inventory table, of course, with the assumption that there are no gaps in the id columns.
In case you cannot avoid the gap, you can create an additional column e.g., seq to maintain the sequences of the rows so that you apply this technique.
In this tutorial, you have learned how to compare successive rows within the same table using the self-join technique.