CODEWITHSUNDEEP
sqlsql-server
Sreedhar
Sreedhar

Reputation: 30045

SQL Server + Select only two records for each masterID in a table

I got a child table that contains 1 to n records linked to the master table via a MasterID column.

How can I select from the child table only the first 5 records for each MasterID?

Upvotes: 2

Views: 477

Answers (2)

LesterDove
LesterDove

Reputation: 3044

Try a regular join where the constraint is a subquery pulling the TOP 5 from the child table. In untested pseudocode:

SELECT A.MasterID, B.*
FROM MasterTable A 
JOIN ChildTable B
ON A.MasterID = B.MasterID 
AND B.ChildID IN (SELECT Top 5 ChildID FROM ChildTable 
    WHERE MasterID = A.MasterID ORDER BY Whatever)

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166506

Using Sql Server CTE and ROW_NUMBER you could try using

DECLARE @ParentTable TABLE(
        ID INT
)

INSERT INTO @ParentTable SELECT 1
INSERT INTO @ParentTable SELECT 2
INSERT INTO @ParentTable SELECT 3

DECLARE @ChildTable TABLE(
        ID INT,
        ParentID INT
)

INSERT INTO @ChildTable SELECT 1, 1
INSERT INTO @ChildTable SELECT 2, 1
INSERT INTO @ChildTable SELECT 3, 1
INSERT INTO @ChildTable SELECT 4, 1
INSERT INTO @ChildTable SELECT 5, 1
INSERT INTO @ChildTable SELECT 6, 1
INSERT INTO @ChildTable SELECT 7, 1

INSERT INTO @ChildTable SELECT 8, 2
INSERT INTO @ChildTable SELECT 9, 2

INSERT INTO @ChildTable SELECT 10, 3
INSERT INTO @ChildTable SELECT 11, 3

;WITH RowNums AS(
        SELECT  pt.ID ParentID,
                ct.ID ChildID,
                ROW_NUMBER() OVER (PARTITION BY pt.ID ORDER BY ct.ID) RowNum
        FROM    @ParentTable pt INNER JOIN
                @ChildTable ct ON pt.ID = ct.ParentID
)
SELECT  ParentID,
        ChildID
FROM    RowNums
WHERE   RowNum <= 5

Upvotes: 2

Related Questions