Limiting the Number of Records Joined How can I perform a query that contains a (one-to-many) join between two tables, which will only join up to a pre-defined number of joined records?

So, lets say I have two tables, one for Matches, and one for Goals, and I want to perform some analysis on the matches based on the first 3 goals that have been scored in it. The idea would be to execute a single query, and I would then have a list of comments that is only comprised of the most recent 5 comments on each post

Matches:

+----------+
| Match ID |
+----------+
| 1        |
+----------+
| 2        |
+----------+
| 3        |
+----------+


Goals:

+---------+----+
| Goal ID | In | 
+---------+----+
| 1       | 2  |
+---------+----+
| 2       | 2  |
+---------+----+
| 3       | 1  |
+---------+----+
| 4       | 3  |
+---------+----+
| 5       | 1  |
+---------+----+
| 6       | 2  |
+---------+----+
| 7       | 3  |
+---------+----+
| 8       | 2  |
+---------+----+
| 9       | 1  |
+---------+----+


Result: (limit to first 2)

+---------+----+
| Goal ID | In | 
+---------+----+
| 1       | 2  |
+---------+----+
| 2       | 2  |
+---------+----+
| 3       | 1  |
+---------+----+
| 4       | 3  |
+---------+----+
| 5       | 1  |
+---------+----+
| 7       | 3  |
+---------+----+
In SQL Server and other systems that support ROW_NUMBER()...

WITH GoalsWithGoalNum (
    SELECT *, 
        ROW_NUMBER() OVER (
            PARTITION BY [In] 
            ORDER BY [Goal ID]
        ) AS GoalNum
    FROM Goals
)
SELECT *
FROM GoalsWithGoalNum
WHERE GoalNum &lt;= 2;