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.