Sunday, May 1, 2011

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.

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,
                    SELECT  id
                    FROM    replies li
                    WHERE   li.post_id = dlo.post_id
                    ORDER BY
                    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 <= 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:

    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


Post a Comment