Deleting repeating values in database

Question

I have a MySql table which is populating daily with price values. Each day it records an entry even if the price hasn't changed. I want to delete some rows that repeat too much. I want to keep the first price and the last price before there is a price change.

Example 1)

   id name     price date
    1 Product1 $6 13/07/2017
    2 Product1 $6 14/07/2017
    3 Product1 $6 15/07/2017
    4 Product1 $7 16/07/2017
    5 Product1 $6 17/07/2017
    6 Product1 $6 18/07/2017
    7 Product1 $6 19/07/2017

From that list records with id 2 and 6 should be deleted with the following result:

   id name     price date
    1 Product1 $6 13/07/2017
    3 Product1 $6 15/07/2017
    4 Product1 $7 16/07/2017
    5 Product1 $6 17/07/2017
    7 Product1 $6 19/07/2017

Example 2)

   id name     price date
    1 Product1 $6 13/07/2017
    2 Product1 $6 14/07/2017
    3 Product1 $6 15/07/2017
    4 Product1 $6 16/07/2017
    5 Product1 $6 17/07/2017
    6 Product1 $6 18/07/2017
    7 Product1 $6 19/07/2017

Here there is no price change so I can delete all records from 2 to 6:

   id name     price date
    1 Product1 $6 13/07/2017
    7 Product1 $6 19/07/2017

Id is not supposed to be one incremental and also date is not updated on daily basis.

Answer 1

You can do this with some creative self-join logic.

Think of three hypothetical rows in the table.

• Row a you want to keep.
• Row b has the same product name and price, and a date 1 day after a. You want to delete this.
• Row c has the same product name and price, and a date 1 day after b. You want to keep this.

So if you can do a self-join to match these three rows, then delete row b.

DELETE b FROM MyTable AS a 
JOIN MyTable AS b ON a.name=b.name AND a.price=b.price AND a.date=b.date + INTERVAL 1 DAY 
JOIN MyTable AS c ON b.name=c.name AND b.price=c.price AND b.date=c.date + INTERVAL 1 DAY;

This works even if there are multiple rows that fit the conditions for row b. It'll delete the first one, and then continue to delete subsequent rows that also fit the conditions.

This works if you use the DATE data type and store your dates as 'YYYY-MM-DD', not 'DD-MM-YYYY'. You should do this anyway.

Answer 2

You want to delete the rows where the product name and price are the same as the rows with the date plus/minus one day.

DELETE row_mid
FROM 
  record_table AS row_mid
  JOIN record_table AS row_prev
  JOIN record_table AS row_next
WHERE
  row_mid.name = row_prev.name 
  AND row_mid.price = row_prev.price
  AND row_mid.date = DATE_SUB(row_prev.date, INTERVAL 1 DAY)
  AND row_mid.name = row_next.name
  AND row_mid.price = row_next.price
  AND row_mid.date = DATE_ADD(row_next.date, INTERVAL 1 DAY);