MySQL query for wordpress: minimum 100 posts per category - delete the rest

Question

I want an SQL statement that will delete all posts except for 100 most recent in each category.

This seems easy - but many posts exist in several categories, so deleting post number 101 on one category might delete post number 89 on another.

I have tried using MySQLs "LIMIT" but its not allowed in subqueries. So I have tried to use ROW_NUMBER() OVER with rownumber >= 100 instead, which works in a sub query - but gives wrong results.

so this works for selecting all posts in a category older than last 100:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY wp_posts.post_date DESC) AS rownumber
  FROM wp_posts
  WHERE (wp_posts.ID IN (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (71) ) ) 
AND (wp_posts.post_type = 'post' OR wp_posts.post_type = 'xdays1')  
) AS foo
WHERE rownumber >= 100

which gets right number of results, 29,602.

but when I put that in another query, the results are wrong:

SELECT wp_posts.*
FROM  wp_posts 
   WHERE 
wp_posts.ID IN

(SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY wp_posts.post_date DESC) AS rownumber
  FROM wp_posts
  WHERE (wp_posts.ID IN (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (71) ) ) 
AND (wp_posts.post_type = 'post' OR wp_posts.post_type = 'xdays1')  
) AS foo
WHERE rownumber >= 100)

results are only 10 rows. This test is just for one category and I want to do it for all categories - so I figure if I can get this working in a subquery I can then add other categories.

What am I doing wrong?

Answer 1

Managed to get it working. This is using four categories with IDs 71,72,73,74

SELECT wp_posts.*
FROM  wp_posts 
   
WHERE 

(wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish')   
   
AND

-- posts discluded if under 100 most recent per category:
(

( wp_posts.ID NOT IN (SELECT ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY wp_posts.post_date DESC) AS rownumber
  FROM wp_posts
  WHERE (wp_posts.ID IN (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (71) ) ) 
AND (wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish')  
) AS foo
WHERE rownumber <= 100
) 
)

AND

( wp_posts.ID NOT IN (SELECT ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY wp_posts.post_date DESC) AS rownumber
  FROM wp_posts
  WHERE (wp_posts.ID IN (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (72) ) ) 
AND (wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish')  
) AS foo
WHERE rownumber <= 100
) 
)

AND

( wp_posts.ID NOT IN (SELECT ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY wp_posts.post_date DESC) AS rownumber
  FROM wp_posts
  WHERE (wp_posts.ID IN (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (73) ) ) 
AND (wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish')  
) AS foo
WHERE rownumber <= 100
) 
)

AND

( wp_posts.ID NOT IN (SELECT ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY wp_posts.post_date DESC) AS rownumber
  FROM wp_posts
  WHERE (wp_posts.ID IN (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (74) ) ) 
AND (wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish')  
) AS foo
WHERE rownumber <= 100
) 
)

)