I have a table named log
in one of MySQL database. And it is used to log the user actions. When it becomes very large in size, I want to delete some records.
SELECT `userid`, `timestamp`
FROM `log`
ORDER `timestamp` ASC
the output is
userid timestamp
2 120000
3 123333
1 123456
1 124444
2 125555
2 126666
1 127777
1 128888
2 129999
3 130000
1 131111
What I want to do is that - I want to keep only last 3 records for each user. So, I need to delete the 4th, 5th, ..., nth records for each user. According to the above sample, the desire output is
userid timestamp
3 123333
2 125555
2 126666
1 127777
1 128888
2 129999
3 130000
1 131111
I know that records can be deleted by using LIMIT
.
DELETE FROM `log` LIMIT 3
deletes only 3 records. It simply cannot get my desire result.
What I have tried is that
DELETE FROM
`log`
WHERE `userid` IN (
SELECT `userid` FROM (SELECT `userid`, COUNT(1) AS C
FROM `log`
GROUP BY `userid`
HAVING C > 3) CountTable ) LIMIT 3
And it is not what I wanted.
Try this:
DELETE l FROM `log` l
LEFT JOIN (SELECT l.userid, l.timestamp
FROM (SELECT l.userid, l.timestamp,
IF(@lastUserId = @lastUserId:=userid, @Idx:=@Idx+1, @Idx:=0) rowNumber
FROM `log` l, (SELECT @lastUserId:=0, @Idx:=0) A
ORDER BY l.userid, l.timestamp DESC
) AS A
WHERE rowNumber < 3
) AS A ON l.userid = A.userid AND l.timestamp = A.timestamp
WHERE A.userid IS NULL
EDIT:
DELETE l FROM `log` l
WHERE NOT EXISTS (
SELECT 1
FROM (SELECT l.userid, l.timestamp,
IF(@lastUserId = @lastUserId:=userid, @Idx:=@Idx+1, @Idx:=0) rowNumber
FROM `log` l, (SELECT @lastUserId:=0, @Idx:=0) A
ORDER BY l.userid, l.timestamp DESC
) AS A
WHERE l.userid = A.userid AND l.timestamp = A.timestamp AND rowNumber < 3
)
Check the SQL FIDDLE DEMO
OUTPUT
| USERID | TIMESTAMP |
|--------|-----------|
| 3 | 123333 |
| 2 | 125555 |
| 2 | 126666 |
| 1 | 127777 |
| 1 | 128888 |
| 2 | 129999 |
| 3 | 130000 |
| 1 | 131111 |