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

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.

From stackoverflow

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

• 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)
  KM : thanks for the link your great blog!

  Quassnoi : You're welcome :)

  Quassnoi : 0xFFFFFFFF is a constant which containt maximal possible INTEGER. Since it has type BINARY, CAST converts it to an integer. MID is the 3rd id for a given group, or a MAXINT if there is no 3rd.

  Quassnoi : You can replace the whole CAST(...) thing with a constant 4294967295 if it confuses you :)

  Quassnoi : @TheLizardKing: Doesn't it look round to you? :) You can use 4,000,000,000 if you're sure you will never have such a big ID in your database :)

• following ian Jacobs idea

  declare @PostID int

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