2

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: 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!

Community
  • 1
  • 1
Colin O'Dell
  • 8,386
  • 8
  • 38
  • 75

2 Answers2

1

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
Aishwar
  • 9,284
  • 10
  • 59
  • 80
0

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?

Sunny Milenov
  • 21,990
  • 6
  • 80
  • 106