Delete Records Except Last Three Records

greatest-n-per-group, mysql, records, sql, sql-delete

Solution

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

Problem

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 ```

Original source