How to Compare Successive Rows within the Same Table in MySQL

Summary: in this tutorial, you will learn how to compare successive rows within the same table in MySQL.

Setting up sample data

First, create a new table called inventory:

CREATE TABLE inventory(
  id INT AUTO_INCREMENT PRIMARY KEY,
  counted_date date NOT NULL,
  item_no VARCHAR(20) NOT NULL,
  qty INT NOT NULL
);Code language: SQL (Structured Query Language) (sql)

In the inventory table:

• The id is an auto-increment column.
• The counted_date is the counted date.
• The item_no is the item code posted to inventory.
• The qty is the accumulated on-hand quantity in inventory.

Second, insert some rows into the inventory table:

INSERT INTO inventory(counted_date,item_no,qty)
VALUES ('2014-10-01','A',20),
	   ('2014-10-02','A',30),
	   ('2014-10-03','A',45),
	   ('2014-10-04','A',80),
	   ('2014-10-05','A',100);Code language: SQL (Structured Query Language) (sql)

Third, retrieve data from the inventory table:

SELECT * FROM inventory;

Output:

+----+--------------+---------+-----+
| id | counted_date | item_no | qty |
+----+--------------+---------+-----+
|  1 | 2014-10-01   | A       |  20 |
|  2 | 2014-10-01   | A       |  30 |
|  3 | 2014-10-01   | A       |  45 |
|  4 | 2014-10-01   | A       |  80 |
|  5 | 2014-10-01   | A       | 100 |
+----+--------------+---------+-----+
5 rows in set (0.00 sec)Code language: JavaScript (javascript)

If you want to determine the received quantity of item A of a particular day, you need to compare the on-hand quantity of that day with its preceding day.

In other words, you need to compare each row with its consecutive row in the inventory table to calculate the difference.

Comparing the current row with the next row within the same table

The following query uses a Common Table Expression (CTE) and the LAG window function to calculate the received quantity of items per day by comparing the on-hand quantity of a particular day with its successive day:

WITH cte AS (
  SELECT 
    item_no, 
    counted_date from_date, 
    LEAD(counted_date, 1) OVER ( ORDER BY counted_date) to_date, 
    qty, 
    LEAD(qty, 1) OVER (ORDER BY counted_date) new_qty 
  FROM 
    inventory
) 
SELECT 
  item_no, 
  from_date, 
  to_date, 
  (new_qty - qty) AS received_qty 
FROM 
  cte 
WHERE 
  to_date IS NOT NULL;Code language: SQL (Structured Query Language) (sql)

Output:

+---------+------------+------------+--------------+
| item_no | from_date  | to_date    | received_qty |
+---------+------------+------------+--------------+
| A       | 2014-10-01 | 2014-10-02 |           10 |
| A       | 2014-10-02 | 2014-10-03 |           15 |
| A       | 2014-10-03 | 2014-10-04 |           35 |
| A       | 2014-10-04 | 2014-10-05 |           20 |
+---------+------------+------------+--------------+
4 rows in set (0.00 sec)Code language: JavaScript (javascript)

How it works.

First, define the cte common table expression to simplify the subsequent query:

WITH cte AS (
  SELECT 
    item_no, 
    counted_date from_date, 
    LEAD(counted_date, 1) OVER ( ORDER BY counted_date) to_date, 
    qty, 
    LEAD(qty, 1) OVER (ORDER BY counted_date) new_qty 
  FROM 
    inventory
) Code language: SQL (Structured Query Language) (sql)

The CTE selects the following columns from the inventory table:

• item_no: The item number.
• counted_date: The date of counting.
• LEAD(counted_date, 1) OVER (ORDER BY counted_date) AS TO_DATE: It uses the LEAD window function to get the next counted_date order by counted_date, which returns the to_date.
• qty: The number of items on the current day.
• LEAD(qty, 1) OVER (ORDER BY counted_date) AS new_qty: It uses the LEAD window function to get the number of items on the next day, which returns the new_qty.

Second, retrieve data from the cte to form the desired result set:

SELECT 
  item_no, 
  from_date, 
  to_date, 
  (new_qty - qty) AS received_qty 
FROM 
  cte 
WHERE 
  to_date IS NOT NULL;Code language: SQL (Structured Query Language) (sql)

The query selects columns from the cte:

• item_no: The item number.
• from_date: The counted date, which is considered as the starting date.
• to_date: The counted date of the next day, obtained using the LEAD function in the CTE.
• new_qty - qty AS received_qty: Calculates the received quantity by subtracting the current day’s quantity (qty) from the next day’s quantity (new_qty).
• WHERE to_date IS NOT NULL: Filters the results to exclude the last day where there is no next day.

Summary

• Use a CTE and LAG window function to compare successive rows within the same table.