Delete all SQL rows except one for a Group

I have a table like this:

Schema (MySQL v5.7)

CREATE TABLE likethis
    (`id` int, `userid` int, `date` DATE)
;
    
INSERT INTO likethis
    (`id`, `userid`, `date`)
VALUES
        (1, 1, "2021-11-15"),
        (2, 2, "2021-11-15"),
        (3, 1, "2021-11-13"),
        (4, 3, "2021-10-13"),
        (5, 3, "2021-09-13"),
        (6, 2, "2021-09-13");

id	userid	date
1	1	2021-11-15
2	2	2021-11-15
3	1	2021-11-13
4	3	2021-10-13
5	3	2021-09-13
6	2	2021-09-13

View on DB Fiddle

I want to delete all records which are older than 14 days, EXCEPT if the user only has records which are older – than keep the “newest” (biggest “id”) row for this user.

Desired target after that action shall be:

id	userid	date
1	1	2021-11-15
2	2	2021-11-15
3	1	2021-11-13
4	3	2021-10-13

i.e.: User ID 1 only has records within the last 14 days: Keep all of them. User ID has a record within the last 14 days, so delete ALL his records which are older than 14 days. User ID 3 has only “old” records, i.e. older than 14 days – so keep only the one newest of those records, even though it’s older than 14 days.

I thought of something like a self join with a subquery where I group by user-id … but can’t really get to it …

Answer

This query could work

DELETE b
FROM likethis a
JOIN likethis b ON a.`userid` = b.`userid` AND a.`date` > b.`date`
WHERE b.`date` < NOW() - INTERVAL 14 DAY

Advertisement

Answer