Delete Records Except Last Three Records

Question

I have a log table in which I want to delete records of each user except last three records.

Schema

DROP TABLE IF EXISTS `log`;
CREATE TABLE `log` (
  `user_id` int(11) DEFAULT NULL,
  `timestamp` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert  into `log`(`user_id`,`timestamp`) values (1,1389257013),(1,1389257014),(1,1389257015),(1,1389257016),(1,1389257017),(2,1389257018),(2,1389257019),(2,1389257020),(2,1389257021),(2,1389257022),(3,1389257023),(3,1389257024);

Current Table:

id    timestamp
1     1389257013
1     1389257014
1     1389257015
1     1389257016
1     1389257017
2     1389257018
2     1389257019
2     1389257020
2     1389257021
2     1389257022
3     1389257023
3     1389257024

Expected Table

id    timestamp    
1     1389257015
1     1389257016
1     1389257017
2     1389257020
2     1389257021
2     1389257022
3     1389257023
3     1389257024

Bookkeeper

Answer 1

Try below SQL:

DELETE FROM log WHERE find_in_set(
    TIMESTAMP, (
        SELECT group_concat(t3) t4 FROM (
            SELECT 1 AS dummy,
            replace(group_concat(TIMESTAMP ORDER BY TIMESTAMP DESC), concat(SUBSTRING_INDEX(group_concat(TIMESTAMP ORDER BY TIMESTAMP DESC), ',', 3), ','), '') t3
            FROM log
            GROUP BY user_id HAVING count(*) > 3
        ) a GROUP BY dummy
    )
)

SQL Fiddle

Xerarch

Much better !! This is bit shorter than previous solution. – Bookkeeper

Answer 2

Try this:

DELETE l FROM `log` l 
WHERE NOT EXISTS (
          SELECT 1
          FROM (SELECT l.user_id, l.timestamp, 
                       IF(@lastUserId = @lastUserId:=user_id, @Idx:=@Idx+1, @Idx:=0) rowNumber 
                FROM `log` l, (SELECT @lastUserId:=0, @Idx:=0) A
                ORDER BY l.user_id, l.timestamp DESC
               ) AS A
          WHERE l.user_id= A.user_idAND l.timestamp = A.timestamp AND rowNumber < 3
         );

Check the SQL FIDDLE DEMO

OUTPUT

| USER_ID |  TIMESTAMP |
|---------|------------|
|       1 | 1389257015 |
|       1 | 1389257016 |
|       1 | 1389257017 |
|       2 | 1389257020 |
|       2 | 1389257021 |
|       2 | 1389257022 |
|       3 | 1389257023 |
|       3 | 1389257024 |

Weeds

Can a query be written for same problem in which MAX(timestamp) is found three times with user_id less than last record of same user? – Bookkeeper

@neeraj Inner query will generate userwise unique row numbers. – Weeds

Ok, Did you see my second comment regarding usage of MAX aggregate function instead of rownumber? – Bookkeeper

@neeraj MAX will return latest data only. You can't find latest 3 records – Weeds

Another Solution :: DELETE FROM log where timestamp IN ( SELECT timestamp FROM ( SELECT user_id, timestamp, rowNumber FROM ( SELECT l.user_id, l.timestamp, IF(@lastuser_id = @lastuser_id:=user_id, @Idx:=@Idx+1, @Idx:=1) rowNumber FROM log l, (SELECT @lastuser_id:=0, @Idx:=0) A ORDER BY l.user_id, l.timestamp DESC ) as res where rowNumber>3 ) AS result ) – Bookkeeper