Im trying to create a query that will delete any records for a user (preferably usrid=2034) where the record count is greater than 50 for each ctoon_id. I've had someone help with it so far, but it isn't incrementing the rnum value properly, so it doesn't know when to start pruning records.
DELETE FROM tbl_users_ctoons
WHERE (usrid, ctoon_id) IN (
SELECT DISTINCT usrid, ctoon_id
FROM (
SELECT
@row_number := CASE
WHEN (@userid = usrid AND @ctoon_id = ctoon_id) THEN @row_number + 1
ELSE 1
END AS rnum,
@userid:=usrid AS usrid,
@ctoon_id:=ctoon_id AS ctoon_id
FROM tbl_users_ctoons
WHERE usrid=2034
ORDER BY usrid, ctoon_id
) sub
WHERE rnum > 50
);
<img src="
http://forums.sqlteam.com/uploads/sqlteam/original/2X/a/ae1e522bf1659de8011fad37600d94b471270995.jpg" width="170" height="500">