How to delete all but keep N latest rows per product in price history table

Question

I have a table in MySQL 5.7.26 where I am storing history of products, I do not have any auto increment column

+------------+---------------------+
| product_id | date_in             |
+------------+---------------------+
|     500 | 2020-04-24 07:10:52 |
|     500 | 2020-04-24 07:12:11 |
|     500 | 2020-04-24 07:16:25 |
|     500 | 2020-04-24 07:18:32 |
|     500 | 2020-04-24 07:18:42 |
|     500 | 2020-04-24 13:51:30 |
|     500 | 2020-04-24 14:01:22 |

|     501 | 2020-04-24 07:10:52 |
|     501 | 2020-04-24 07:12:11 |
|     501 | 2020-04-24 07:16:25 |
|     501 | 2020-04-24 07:18:32 |
|     501 | 2020-04-24 07:18:42 |
|     501 | 2020-04-24 13:51:30 |
|     501 | 2020-04-24 14:01:22 |
+------------+---------------------+

I want to keep only last 2 history rows per product, and delete all of them, so it will look like this

+------------+---------------------+
| product_id | date_in             |
+------------+---------------------+

|     500 | 2020-04-24 13:51:30 |
|     500 | 2020-04-24 14:01:22 |


|     501 | 2020-04-24 13:51:30 |
|     501 | 2020-04-24 14:01:22 |
+------------+---------------------+

GMB · Answer 1 · 2020-04-24T15:29:21.050

1

You can use the delete/join syntax, along with row_number() (the latter requires MySQL 8.0)`:

delete t
from mytable t
inner join (
    select 
        product_id, 
        date_in, 
        row_number() over(partition by product_id order by date_in desc) rn
    from mytable t
) t1 
    on  t1.product_id = t.product_id 
    and t1.date_in = t.date_in 
    and t1.rn > 2

In earlier versions, we can elumate row_number() with a subquery - assuming no duplicates on (product_id, date_in):

delete t
from mytable t
inner join (
    select 
        product_id, 
        date_in,
        (select count(*) from mytable t2 where t2.product_id = t1.product_id and t2.date_in >= t1.date_in) rn
    from mytable t1
) t1 
    on  t1.product_id = t.product_id 
    and t1.date_in = t.date_in 
    and t1.rn > 2;

edited Apr 24 '20 at 15:29

answered Apr 24 '20 at 14:18

GMB

216,147
25
84
135

`You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by product_id order by date_in desc) rn` – Umair Ayub Apr 24 '20 at 14:27
@Umair: which version of MySQL are you running? `select version()` – GMB Apr 24 '20 at 14:35
5.7.26-0ubuntu0.18.10.1 – Umair Ayub Apr 24 '20 at 14:40
@Umair: OK. The solution I posted requires MySQL 8.0, as explained in the answer. I added another query to my answer, that works on earlier versions. – GMB Apr 24 '20 at 15:30
Its been 2000 seconds and query has not finished yet – Umair Ayub Apr 25 '20 at 07:07

score 0 · Answer 2 · answered Apr 24 '20 at 14:53

Try this. It will work in all of the mysql versions

DELETE p1 
FROM
    products p1
    JOIN (
    SELECT
        product_id,
        date_in 
    FROM
        (
        SELECT
            p3.product_id,
            p3.date_in,
            ( CASE @grouping WHEN p3.product_id THEN @rank := @rank + 1 ELSE @rank := 1 END ) AS rank,
            @grouping := p3.product_id 
        FROM
            products p3,
            ( SELECT @rank := 0, @grouping := 0 ) AS tt 
        ORDER BY
            p3.product_id,
            p3.date_in DESC 
        ) tt2 
    WHERE
        rank > 2 
    ) p2 ON p1.product_id = p2.product_id 
    AND p1.date_in = p2.date_in

How to delete all but keep N latest rows per product in price history table

2 Answers2