How to write a "balanced" sql select query

Question

Forgive me if the title of this question is unclear as I'm struggling to find a name for what I'm looking for. So please bear with me as I explain:

Let's say I have the following tables:

Now let's say there are 5 entries in feeds table:

the great splog which publishes 100 posts a day

unknown blog 1 to unknown blog 4 which publish 1 or 2 post(s) a day

We are going to fetch these feeds periodically and save their posts in posts table.

But then, if I select the last 10 entries from posts table, there is a big chance that all the 10 posts belong to the great splog, simply because it published more posts today than other 4 unknown guys.

But I don't want 10 posts from the same feed, I want the select query to understand this situation and pick -for example- only 2 entries written by the great splog and 8 entries written by other unknown blogs from posts table.

How do you go implementing such a query?

Answer 1

To take 3 diferent, and 7 additional:

(SELECT * FROM posts WHERE post_id IN (SELECT MAX(post_id) FROM posts GROUP BY post_feed_id) ORDER BY post_id DESC LIMIT 3)
UNION
(SELECT * FROM posts ORDER BY post_id DESC LIMIT 7)
ORDER BY post_id DESC

UPDATE: Above code only gives 9, so do some more capsuling to get limit on the right place.

SELECT * FROM 
(
    SELECT * FROM 
    (
        (
            SELECT * FROM posts WHERE post_id IN (
                SELECT MAX(post_id) FROM posts GROUP BY post_feed_id
                ) ORDER BY post_id DESC LIMIT 3
        )
        UNION
        (
            SELECT * FROM posts ORDER BY post_id DESC
        )
    ) AS temp LIMIT 10
) AS temp2 ORDER BY post_id DESC;

Answer 2

You can get the last post from each feed using this statement:

select p.*
from posts p join
     (select p.post_feed_id, max(p.post_id) as maxpi
      from posts p
      group by p.post_feed_id
     ) pf
     on p.post_id = pf.maxpi;

Before you can think about getting more than one, you have to think about how you want to allocate them across the feeds.