How do to keep latest N rows of each user's and delete the rest of rows in mysql?

Question

I have a user_accounts table with 6 columns:

id, user_id, amount, type, total_balance, created_at
1   101        10    debit       90       2018-09-14 20:10:49
2   101        30    credit     120       2018-09-14 20:10:52
3   102       210    credit     310       2018-09-14 21:10:52
4   102        10    debit      300       2018-09-14 21:10:54
5   103        10    credit     110       2018-09-14 21:10:54 
6   104        15    credit     115       2018-09-14 21:11:59

I want to keep latest n rows of each user's and delete the rest of rows.

What is the best way to construct this query in mysql?

On larger data sets, it can be quicker to create a new table with only the retained data, and then delete the old table, and then rename and index the new table. But, first be absolutely sure that you really want to delete data! — Strawberry, Sep 19 '18 at 06:41
Possible duplicate of [Using LIMIT within GROUP BY to get N results per group?](https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) — Nick, Sep 19 '18 at 06:42

D-Shih · Accepted Answer · 2018-09-19T07:13:05.543

You can try to make row number by each userId and order by created_at in a subquery.

then delete ID on each userId by the rn

From my sample, I only keep the first least row so I set rn > 1

CREATE TABLE T(
  id  int,
  user_id int,
  amount int, 
  type varchar(50), 
  total_balance int, 
  created_at datetime
);



insert into T values (1,101,10 ,'debit',90 ,'2018-09-14 20:10:49');
insert into T values (2,101,30 ,'credit',120,'2018-09-14 20:10:52');
insert into T values (7,101,30 ,'credit',120,'2018-09-16 20:10:52');
insert into T values (3,102,210,'credit',310,'2018-09-14 21:10:52');
insert into T values (4,102,10 ,'debit',300,'2018-09-14 21:10:54');
insert into T values (5,103,10 ,'credit',110,'2018-09-14 21:10:54'); 
insert into T values (6,104,15 ,'credit',115,'2018-09-14 21:11:59');


DELETE FROM T
WHERE ID IN (
    SELECT ID 
    FROM 
    (
       SELECT ID,(
        SELECT COUNT(*) 
        FROM T tt
        WHERE
          tt.user_id = t1.user_id 
        AND 
          tt.created_at >= t1.created_at
        ORDER BY 
           tt.created_at desc
      ) rn
      FROM T t1
    ) deltable
    where rn > 1 # n least rows you want to keep.
);

Query 1:

SELECT * FROM T

Results:

| id | user_id | amount |   type | total_balance |           created_at |
|----|---------|--------|--------|---------------|----------------------|
|  7 |     101 |     30 | credit |           120 | 2018-09-16T20:10:52Z |
|  4 |     102 |     10 |  debit |           300 | 2018-09-14T21:10:54Z |
|  5 |     103 |     10 | credit |           110 | 2018-09-14T21:10:54Z |
|  6 |     104 |     15 | credit |           115 | 2018-09-14T21:11:59Z |

That can be done very easily as I am using auto incremented id. `DELETE FROM user_accounts WHERE id NOT IN (SELECT MAX(id) FROM user_accounts GROUP BY user_id);` But I want to keep latest 100 records of each user. Is there any possible way to do this? — Sabyasachi Ghosh, Sep 19 '18 at 07:15
In my query you just set `rn > 100` to keep least 100 rows, your query only keep the least `ID`, But my can keep `n` rows — D-Shih, Sep 19 '18 at 07:18
Sorry. I missed that.Thank you. But is it efficient as it creates temp table and joining. In my table has more than 100K records. — Sabyasachi Ghosh, Sep 19 '18 at 07:19
https://www.db-fiddle.com/f/mJypKEbTV36SMNVUVcoopK/0 here is another sample keep least 2 rows Id from each user — D-Shih, Sep 19 '18 at 07:20

score 0 · Answer 2 · answered Sep 19 '18 at 07:01

0

You can try with following query. Here N is number of rows you want to keep

DELETE FROM user_accounts WHERE id NOT IN (select id FROM user_accounts ORDER BY created_at DESC LIMIT N)

answered Sep 19 '18 at 07:01

Kirtee

141
9

`select id FROM user_accounts ORDER BY created_at DESC LIMIT N` it will not return the latest N row of `each user` – Sabyasachi Ghosh Sep 19 '18 at 07:09
@SabyasachiGhosh You never mentioned, that you want to return those values. You only wanted All values except n values to be deleted. – Sep 19 '18 at 07:13
@D.Schaller, Nope. It is clearly mentioned in the question. – Sabyasachi Ghosh Sep 19 '18 at 07:16
`latest n rows of each user's...` – Sabyasachi Ghosh Sep 19 '18 at 07:17
@SabyasachiGhosh Seems like we both misunderstood each other. – Sep 19 '18 at 07:20

How do to keep latest N rows of each user's and delete the rest of rows in mysql?

2 Answers2