compare two rows in same table in mysql

How to Compare Two Rows from Same Table

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 | 2020-10-01 |    100 |
|    2 | 2020-10-02 |    125 |
|    3 | 2020-10-03 |    140 |
|    4 | 2020-10-04 |    160 |
+------+------------+--------+

Bonus Read : How to Compare Two Columns in MySQL

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. Here’s the SQL query to compare each row with previous row.

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

In the above query, we join sales table with itself using an INNER JOIN condition g2.id=g1.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.

Hopefully, now you can easily compare two rows in same table in MySQL.

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

mm

About Ubiq

Ubiq is a powerful dashboard & reporting platform. Build dashboards, charts & reports for your business in minutes. Try it for free!