MySQL: how to keep top 1000 rows for each value of a column

Question

hi,

I need some help with this query: I want to delete the following rows from my table. The table has 4 columns: UserID, UserName, Tag, Score.

I want to keep only the top 1000 users with highest score, for each Tag.

So I need to order by score, and to keep the first 1000 users for each tag.

Thanks

Answer 1

Maybe a temporary table, who is filled with a select clause

SELECT * FROM table ORDER BY Score DESC LIMIT 1000;

I dont have the syntax handy for creating the temporary table, but that will atleast get your the rows you want.

Be careful with delete commands... Better to create a new view/table.

Answer 2

Based on the other answer,

DELETE FROM table t1
WHERE UserID NOT IN (SELECT userID FROM table t2
                     WHERE t2.tag = t1.tag
                     ORDER BY Score DESC LIMIT 1000);

This assumes that UserID is a unique/primary key field. If it's not, you could do something like this:

DELETE FROM table t1
WHERE UserID||' '||Tag NOT IN (SELECT UserID||' '||Tag FROM table t2
                     WHERE t2.tag = t1.tag
                     ORDER BY Score DESC LIMIT 1000);

Putting the spaces in because I know I've had to do similar on Oracle, not sure how MySQL would handle it without.

Answer 3

You should be able to fix this with a subquery although I'm not completely sure if MySQL supports that within delete queries. I do know that it can give problems with update queries (I've had some segfaults in MySQL because of this)

Either way... this should work (atleast, it would in Postgres)

DELETE FROM table WHERE id NOT IN (SELECT id FROM table ORDER BY Score DESC LIMIT 1000)

Assuming that id is your primary key ofcourse.

Solution using a temporary table:

Do note that you'll have to repeat this process for every tag. I can't think of a way to do it in 1 query in MySQL 5.0

CREATE TEMPORARY TABLE top_users
SELECT * FROM table
WHERE Tag = ...
GROUP BY UserID
ORDER BY Score DESC
LIMIT 1000;

DELETE FROM table WHERE Tag = ...;

INSERT INTO table
SELECT * FROM top_users;