SQL Query: DELETE all rows except for the xx most-recent

Question

I'm writing a script to check RSS feeds on regular intervals and need to prune out old articles. I came across this answer which seems really close to what I'm looking for: http://stackoverflow.com/questions/578867/sql-query-delete-all-records-from-the-table-except-latest-n

I need a similar solution that works the same way, except it keeps N articles per feed (and not N articles in general). Each article has a field named "RSSFeedID" which references a specific feed in another table. So for each RSSFeedID, I need to keep only N articles.

Any idea on how to do this? Either the raw SQL query or LINQ-to-SQL code would be very helpful. Thanks!

Answer 1

This is overkill. Why not do this on per feed basis when you add a new article to a specific feed, using the same approach?

Answer 2

If your two tables are called RSSFeeds, RSSPosts, here is how you could do it. Pseudocode provided.

idList = query("SELECT unique id FROM RSSFeeds");
for each id in idList
    query("
    DELETE FROM RSSPosts 
    WHERE id NOT IN 
        (SELECT id 
         FROM RSSPosts 
         WHERE RSSFeedId = @id
         ORDER BY id DESC 
         LIMIT 10)
    ");
end