Menu
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
604
METHOD 1 : Using LIMIT clause in descending orderof specified rows from specifies row. We will retrieve last 5 rows in descending order using LIMIT and ORDER BY clauses and finally make the resultant rows ascending. Since Employee table has IDs, we will perform ORDER BY ID in our query.
This one-liner is the simplest query in the list, to get the last 3 number of records in a table. The TOP clause in SQL Server returns the first N number of records or rows from a table. Applying the ORDER BY clause with DESC, will return rows in descending order. Hence, we get the last 3 rows.
Another way to delete multiple rows is to use the IN operator. DELETE FROM table_name WHERE column_name IN (value 1, value 2, value 3, etc...); If you want to delete all records from the table then you can use this syntax.
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 |
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
