How to Compare Two Rows from Same Table

Last updated on September 2nd, 2024 at 06:48 am

Sometimes you may need to compare successive rows, or compare each row with previous row in MySQL table. In this article, we will look at how to compare two rows from same table using self-joins. You can use it to compare consecutive rows or get difference between two rows.

How to compare successive rows in MySQL

Let’s say you have the following table sales (id, order_date, amount) and amount column contains total cumulative sales data.

mysql> create table sales(id int auto_increment primary key,
 order_date date, amount int);

mysql> insert into sales(order_date, amount)
     values('2020-10-01',100),
     ('2020-10-02',125),
     ('2020-10-03',140),
     ('2020-10-04',160);

mysql> select * from sales;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | 2024-10-01 |    100 |
|    2 | 2024-10-02 |    125 |
|    3 | 2024-10-03 |    140 |
|    4 | 2024-10-04 |    160 |
+------+------------+--------+

Bonus Read : How to Compare Two Columns in MySQL

1. Using INNER JOIN

In our example, if you want to find out sales made on each day then you need to compare each row’s amount value with that of previous row. You can do this using INNER JOIN. Here’s the SQL query to compare each row with previous row, that is, the amount earned on one day with that earned on the previous day.

mysql> SELECT
         t1.id, t1.order_date,t1.amount,
         (t2.amount - t1.amount) AS daily_amount
     FROM
         sales t1 INNER JOIN sales t2 ON t2.id = t1.id + 1;
+------+------------+--------+--------------+
| id   | order_date | amount | daily_amount |
+------+------------+--------+--------------+
|    1 | 2024-10-01 |    100 |           25 |
|    2 | 2024-10-02 |    125 |           15 |
|    3 | 2024-10-03 |    140 |           20 |
+------+------------+--------+--------------+

In the above query, we join sales table with itself using an INNER JOIN condition t2.id=t1.id + 1 that allows you to compare each row with its previous row.

Bonus Read : How to Get Every Nth Row in MySQL

Please note, this condition depends on the fact that our id column has consecutive numbers. Most tables have an auto increment primary key column so it should work in most cases. If they differ by some other constant, you can modify the INNER JOIN condition accordingly.

2. Using Self Join

Alternatively, you can also use self joins to perform the same comparison. Here is the syntax to perform self joins in MySQL.

SELECT column1, column2, ...
FROM table1 t1, table1 t2
WHERE condition1 ... ;

Here is the SQL query to compare one table row with its previous one.

SELECT A.id, A.order_date, A.amount,
         (B.amount - A.amount) AS daily_amount
FROM sales A, sales B
WHERE B.id = A.id + 1;

Conclusion

Hopefully, now you can easily compare two rows in same table in MySQL. In this article, we have learnt couple of ways to compare one row in MySQL table with its previous row. You can use either of these methods as per your requirement.

Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.