0

Basically, I have a table where I store messages for each user. Each user is allocated 1,000 messages, by 30 days.

id user_id message created_at
1 12 ... 2020-01-01 16:30:45
2 45 ... 2020-01-02 16:31:12
3 12 ... 2020-01-02 16:61:15

My intention is to run a single SQL statement to delete the exceeding messages of each user by quantity, and those older than 30 days.

The only solution I have so far is to get the user_id using a GROUP BY clause, and iterate over each with a subquery.

4
  • 1
    I removed the conflicting DBMS tags. Please add only one tag for the database product you are really using.
    – user1822
    Aug 19, 2021 at 5:13
  • 2
    If the number of messages for one user exceeds 1000 but all are younger than 30 days, do you still want to delete the excess messages? Do you want to delete the oldest excess message or the newest?
    – user1822
    Aug 19, 2021 at 6:56
  • Please provide a fiddle (dbfiddle.uk) with your table structures and (sample) data along with your desired result. Then copy that back here and also include the link to the fiddle - help us to help you!
    – Vérace
    Aug 19, 2021 at 7:08
  • have you mysql 8?
    – nbk
    Aug 19, 2021 at 7:52

1 Answer 1

1

You can use ROW_NUMBER in a self-joined DELETE statement:

DELETE t
FROM YourTable t
JOIN (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY t.created_at DESC) AS rn
  FROM YourTable t
) t2 ON t2.id = t.id
WHERE t2.rn > 1000 OR t2.created_at < DATE_SUB(CURDATE(), INTERVAL 30 DAY);

This works for MySQL.

In SQL Server you can modify the derived table directly

DELETE t
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY t.created_at DESC) AS rn
  FROM YourTable t
) t
WHERE t.rn > 1000 OR t.created_at < DATE_ADD(day, -30, GETDATE());
2
  • This is non-standard SQL, please mention for which DBMS this works
    – user1822
    Aug 19, 2021 at 15:04
  • OK done that now Aug 19, 2021 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.