Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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  |
+---------+----+
share|improve this question
1  
In isn't a good column name, btw... –  Rob Farley Apr 25 at 9:32
    
Yeah, why don't you name it MatchID? –  ypercube Apr 25 at 9:59
    
It was for the sake of an example, and I couldn't be bothered to reformat it :) –  cgoddard Apr 25 at 10:23
    
Which DBMS are you using? Postgres? Oracle? –  a_horse_with_no_name Apr 25 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. –  user1598390 Apr 25 at 15:59

2 Answers 2

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;
share|improve this answer

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
share|improve this answer
1  
Table variable assumes SQL-Server. And COUNT() OVER (... ORDER BY ...) works only in SQL-Server 2012+. ROW_NUMBER() is available in earlier versions. –  ypercube Apr 25 at 11:39
    
Ah, so THAT's why COUNT() OVER() confused me. –  Mark Sinkinson Apr 25 at 12:18
    
Yeah that's fair. I have marked Robs answer up –  James Anderson Apr 25 at 15:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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