CODEWITHSUNDEEP
mysql
rockstardev
rockstardev

Reputation: 13527

MySQL: Delete all but 20 rows from a table?

I have a table with hundreds of thousands of rows, assigned to a user. e.g.:

itemid | userid | etc
1      | 1      | etc
2      | 1      | etc
3      | 1      | etc
4      | 3      | etc
5      | 3      | etc
6      | 3      | etc
etc    | etc    | etc

A user can have any number of items assigned to him or her. I.e. Any number from 0 to infinity. My problem is I want an SQL query that will delete all items for each user, but keep 20. If the user has less than 20, for example, only 10 items assigned, it must keep all 10.

How do I do that?

UPDATE

If the user has 50 items, with ids 1 - 50, it must return items 30 - 50. In other words, the last 20 inserted items for that user.

Upvotes: 2

Views: 611

Answers (4)

MythThrazz
MythThrazz

Reputation: 1647

That code works as follows:

  • Creates a new column: count - which counts the items for each user
  • Orders the results by itemid descending - so that the first events for user get the highest ids
  • Selects/deletes all of the rows with count > 20 - leaving the last 20 (or less) events intact

SELECT: 

SET @count=1;
SET @last_user_id=0;

SELECT * FROM (
SELECT *,
case 
when (@last_user_id = userid) 
then 
@count:=@count+1
else @count:=1 end as count,
case 
when (@last_user_id!=userid) 
then 
@last_user_id:=userid
end
as new
FROM <tablename> ORDER BY userid, itemid DESC) AS inner_table WHERE count > 20

DELETE: 

SET @count=1;
SET @last_user_id=0;

DELETE FROM (
SELECT *,
CASE 
WHEN (@last_user_id = userid) 
THEN  
@count:=@count+1
ELSE 
@count:=1 
END 
AS count,
CASE 
WHEN (@last_user_id!=userid) 
THEN  
@last_user_id:=userid
END 
AS new
FROM <tablename> ORDER BY userid, itemid DESC) AS inner_table WHERE count > 20

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

try this

     delete from Fruits where itemid  
     not in (select itemid from (select itemid from Fruits where userid = 1
     ORDER BY itemid desc limit 20)x  )
     and userid=1

DEMO HERE

Upvotes: 0

rockstardev
rockstardev

Reputation: 13527

This worked for me:

DELETE FROM table WHERE itemid NOT IN ( 
      SELECT itemid 
        FROM ( 
          SELECT * 
          FROM table
          WHERE userid = 1
          ORDER BY itemid  DESC 
          LIMIT 20
        ) x 
    ) 
    AND userid = 1

Upvotes: -1

Strawberry
Strawberry

Reputation: 33935

Let's say we want to delete all but the two most recent entries for each user...

CREATE TABLE my_table(itemid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,userid INT NOT NULL);

INSERT INTO my_table VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 3),
(5, 3),
(6, 3);

SELECT * FROM my_table;
+--------+--------+
| itemid | userid |
+--------+--------+
|      1 |      1 |
|      2 |      1 |
|      3 |      1 |
|      4 |      3 |
|      5 |      3 |
|      6 |      3 |
+--------+--------+

Here's a query to select the rows for deletion...

SELECT a.* 
  FROM my_table a 
  LEFT 
  JOIN 
     ( SELECT x.*, COUNT(*) FROM my_table x JOIN my_table y ON y.userid = x.userid AND y.itemid >= x.itemid GROUP BY x.itemid HAVING COUNT(*) <=2)b 
    ON b.itemid = a.itemid 
 WHERE b.itemid IS NULL;
+--------+--------+
| itemid | userid |
+--------+--------+
|      1 |      1 |
|      4 |      3 |
+--------+--------+

...and here's a query to remove them...

DELETE a 
  FROM my_table a 
  LEFT 
  JOIN 
     ( SELECT x.*, COUNT(*) FROM my_table x JOIN my_table y ON y.userid = x.userid AND y.itemid >= x.itemid GROUP BY x.itemid HAVING COUNT(*) <=2)b 
    ON b.itemid = a.itemid 
 WHERE b.itemid IS NULL;

SELECT * FROM my_table;
+--------+--------+
| itemid | userid |
+--------+--------+
|      2 |      1 |
|      3 |      1 |
|      5 |      3 |
|      6 |      3 |
+--------+--------+

Upvotes: 2

Related Questions