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
  • 7,799
  • 8
  • 38
  • 74

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
  • 8,754
  • 9
  • 57
  • 77
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,386
  • 5
  • 78
  • 105