Aaron Li's Blog

My senario: Get latest NumberOfPosts posts for every discussion which meet myCondition

        SELECT
            DiscussionTopPosts.PostID
        From (
            SELECT  p.DiscussionID, p.PostID, ROW_NUMBER() OVER (PARTITION BY p.DiscussionID ORDER BY p.DateCreated DESC) AS RowNumber
            FROM Posts p
            WHERE
                p.DiscussionID IN
                (
                SELECT d.DiscussionID
                FROM Discussions d
                Where myCondition
                )
            AND p.IsActive = 1
            GROUP BY p.DiscussionID, p.PostID, p.DateCreated
            )
            DiscussionTopPosts
        WHERE
          DiscussionTopPosts.RowNumber <= @NumberOfPosts

for more reference, see http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/More-SQL-Server-2005-Solutions.aspx