2

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  |
+---------+----+
5
  • 1
    In isn't a good column name, btw...
    – Rob Farley
    Commented Apr 25, 2014 at 9:32
  • Yeah, why don't you name it MatchID? Commented Apr 25, 2014 at 9:59
  • It was for the sake of an example, and I couldn't be bothered to reformat it :)
    – topherg
    Commented Apr 25, 2014 at 10:23
  • Which DBMS are you using? Postgres? Oracle?
    – user1822
    Commented Apr 25, 2014 at 11:40
  • This question has nothing to do with joins. The question is how to get a limited number of rows from any query, regardless it cointains a join or not. Commented Apr 25, 2014 at 15:59

2 Answers 2

4

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 <= 2;
0

You can use a table variable and a windowing function to do this.

Declare @Goals
([Match id] int
,[Goal Id] int
,[GoalCount] int)

Insert into @Goals
SELECT [In], [Goal Id], COUNT([Goal Id]) OVER (PARTITION BY [In] ORDER BY [Goal Id]) AS [GoalCount]
FROM Goals 

Select * 
From Matches m 
JOIN @Goals g on m.[Match Id] = g.[Match Id] and g.GoalCount <= 3
3
  • 1
    Table variable assumes SQL-Server. And COUNT() OVER (... ORDER BY ...) works only in SQL-Server 2012+. ROW_NUMBER() is available in earlier versions. Commented Apr 25, 2014 at 11:39
  • Ah, so THAT's why COUNT() OVER() confused me. Commented Apr 25, 2014 at 12:18
  • Yeah that's fair. I have marked Robs answer up Commented Apr 25, 2014 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.