Select N rows from a table with a non-unique foreign key

Question

I have asked a similar question before and while the answers I got were spectacular I might need to clearify.

Just like This question I want to return N number of rows depending on a value in a column.

My example will be I have a blog where I want to show my posts along with a preview of the comments. The last three comments to be exact.

I have have I need for my posts but I am racking my brain to get the comments right. The comments table has a foreign key of post_id which obviously multiple comments can be attached to one post so if a post has 20 comments then I just want to return the last three. What makes this somewhat tricky is I want to do it in one query and not a "limit 3" query per blog post which makes rendering a page with a lot of posts very query heavy.

SELECT *
FROM replies
GROUP BY post_id
HAVING COUNT( post_id ) <=3

This query does what I want but only returns one of each comment and not three.

Answer 1

Do you track comment date? You can sort those results to grab only the 3 most recent ones.

Answer 2

SELECT  l.*
FROM    (
        SELECT  post_id,
                COALESCE(
                (
                SELECT  id
                FROM    replies li
                WHERE   li.post_id = dlo.post_id
                ORDER BY
                        li.post_id, li.id
                LIMIT 2, 1
                ), CAST(0xFFFFFFFF AS DECIMAL)) AS mid
        FROM    (
                SELECT  DISTINCT post_id
                FROM    replies dl
                ) dlo
        ) lo, replies l
WHERE   l.replies >= lo.replies
        AND l.replies <= lo.replies
        AND l.id <= lo.mid

Having an index on replies (post_id, id) (in this order) will greatly improve this query.

Note the usage of l.replies >= lo.replies AND l.replies <= lo.replies: this is to make the index to be usable.

See the article in my blog for details:

• Advanced row sampling (how to select N rows from a table for each GROUP)

Answer 3

following ian Jacobs idea

declare @PostID int

select top 3 post_id, comment
from replies
where post_id=@PostID
order by createdate desc