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 |
+---------+----+
Best Answer
In SQL Server and other systems that support ROW_NUMBER()...