Mysql – How to remove only consecutive duplicate fields when the id is not consecutive in MySQL

MySQL

In this table, I have a price change history. The prices are checked regularly and as you can see, sometimes the price does not change. How can I take a specific sku and delete all cases where a duplicate price is consecutively measured. As you can see, on 08-08, the price changed to 79.47. Then, it stayed that way until 08-11 when it changed to 79.41. I don't need all the repeated 79.47 since I can just assume that the price didn't change. So, I just need each point in time that the price changed, and that will allow me to save a lot of space.

╔══════════╦════════╦════════╦═════════════════════╗
║    id    ║  sku   ║ amount ║        date         ║
╠══════════╬════════╬════════╬═════════════════════╣
║   225200 ║ 388744 ║ 60     ║ 2014-08-02 22:08:18 ║
║  1571656 ║ 388744 ║ 79.47  ║ 2014-08-06 20:53:36 ║
║  1572002 ║ 388744 ║ 79.47  ║ 2014-08-06 20:53:42 ║
║  2092567 ║ 388744 ║ 79.45  ║ 2014-08-07 01:22:13 ║
║  2608362 ║ 388744 ║ 79.44  ║ 2014-08-07 05:50:45 ║
║  4403138 ║ 388744 ║ 79.44  ║ 2014-08-07 18:59:02 ║
║  4935625 ║ 388744 ║ 79.44  ║ 2014-08-07 23:18:56 ║
║  5356014 ║ 388744 ║ 79.47  ║ 2014-08-08 03:41:30 ║
║  5703764 ║ 388744 ║ 79.47  ║ 2014-08-08 08:00:47 ║
║  8559295 ║ 388744 ║ 79.47  ║ 2014-08-09 22:40:42 ║
║  8878123 ║ 388744 ║ 79.47  ║ 2014-08-10 02:54:05 ║
║  9204053 ║ 388744 ║ 79.47  ║ 2014-08-10 07:13:06 ║
║  9538507 ║ 388744 ║ 79.47  ║ 2014-08-10 11:27:51 ║
║  9863478 ║ 388744 ║ 79.47  ║ 2014-08-10 15:44:34 ║
║ 10184051 ║ 388744 ║ 79.47  ║ 2014-08-10 20:02:29 ║
║ 10503334 ║ 388744 ║ 79.41  ║ 2014-08-11 00:18:44 ║
║ 10821782 ║ 388744 ║ 79.33  ║ 2014-08-11 04:34:22 ║
║ 11135386 ║ 388744 ║ 79.33  ║ 2014-08-11 08:52:31 ║
║ 11446160 ║ 388744 ║ 79.33  ║ 2014-08-11 13:07:56 ║
║ 11760103 ║ 388744 ║ 79.33  ║ 2014-08-11 17:26:09 ║
║ 12074366 ║ 388744 ║ 79.33  ║ 2014-08-11 21:42:37 ║
║ 12399508 ║ 388744 ║ 79.47  ║ 2014-08-12 02:13:05 ║
║ 12726720 ║ 388744 ║ 79.47  ║ 2014-08-12 06:45:28 ║
║ 12726970 ║ 388744 ║ 79.47  ║ 2014-08-12 06:45:37 ║
║ 13059695 ║ 388744 ║ 79.47  ║ 2014-08-12 11:16:35 ║
║ 13406731 ║ 388744 ║ 79.47  ║ 2014-08-12 15:53:01 ║
║ 13750598 ║ 388744 ║ 79.48  ║ 2014-08-12 20:24:4  ║
╚══════════╩════════╩════════╩═════════════════════╝

So, after the query, it would hopefully look like this:

    ╔══════════╦════════╦════════╦═════════════════════╗
    ║    id    ║  sku   ║ amount ║        date         ║
    ╠══════════╬════════╬════════╬═════════════════════╣
    ║   225200 ║ 388744 ║ 60     ║ 2014-08-02 22:08:18 ║
    ║  1571656 ║ 388744 ║ 79.47  ║ 2014-08-06 20:53:36 ║
    ║  2092567 ║ 388744 ║ 79.45  ║ 2014-08-07 01:22:13 ║
    ║  2608362 ║ 388744 ║ 79.44  ║ 2014-08-07 05:50:45 ║
    ║  5356014 ║ 388744 ║ 79.47  ║ 2014-08-08 03:41:30 ║
    ║ 10503334 ║ 388744 ║ 79.41  ║ 2014-08-11 00:18:44 ║
    ║ 10821782 ║ 388744 ║ 79.33  ║ 2014-08-11 04:34:22 ║
    ║ 12399508 ║ 388744 ║ 79.47  ║ 2014-08-12 02:13:05 ║
    ║ 13750598 ║ 388744 ║ 79.48  ║ 2014-08-12 20:24:4  ║
    ╚══════════╩════════╩════════╩═════════════════════╝

Best Answer

This should do the job :

SELECT
  ph1.sku, 
  ph1.id as ph1Id, 
  ph2.id as ph2Id, 
  ph1.histDate,
  ph2.amount as oldAmount, 
  ph1.amount as newAmount 
FROM priceHistory as ph1 
LEFT JOIN priceHistory as ph2 ON
ph2.id = 
(SELECT max(id) FROM priceHistory AS ph3 WHERE ph3.id < ph1.id and ph1.SKU = ph3.SKU)
WHERE ph1.amount <> ph2.amount OR 
      ph2.amount is null

Here is the fiddle.

Related Question