You can do that with ROW_NUMBER OVER()
Here's my template for that construction
SELECT
M.MainCol1
, M.MainCol2
, C.ChildCol1
, C.ChildCol2
-- , C.ChildOtherCol1
-- , C.ChildOtherCol2
-- , C_RowNumber -- Not normally required in the results
FROM dbo.MyMainTable AS M
JOIN
(
SELECT [C_RowNumber] = ROW_NUMBER()
OVER
(
PARTITION BY C.MatchColumn1
, C.MatchColumn2
ORDER BY
-- ** NOTE: This Sort Order must present the Limit Row(s) to RETAIN FIRST
C.SortColumn1
, C.SortColumn2
-- , CP.PartitionSortColumn1
-- , CP.PartitionSortColumn2
)
, [C_GroupRowCount] = COUNT(*)
OVER
(
PARTITION BY C.MatchColumn1
, C.MatchColumn2
)
, [C_GroupMaxDate] = MAX(C.ChildDateColumn)
OVER
(
PARTITION BY C.MatchColumn1
, C.MatchColumn2
)
, [C_TotalRowCount] = COUNT(*) OVER()
, [C_TotalRowNumber] = ROW_NUMBER()
OVER
(
ORDER BY
C.SortColumn1
, C.SortColumn2
-- , CP.PartitionSortColumn1
-- , CP.PartitionSortColumn2
)
, C.MatchColumn1
, C.MatchColumn2
, C.ChildCol1
, C.ChildCol2
-- , C.ChildSortCol1
-- , C.ChildSortCol1
-- , CO.ChildOtherCol1
-- , CO.ChildOtherCol2
FROM dbo.MyChildTable AS C
-- JOIN ChildOtherTableRequiredForTheSELECT AS CO
-- JOIN ChildOtherTableOnlyUsedForPartition_OrderBy AS CP
WHERE C.FilterColumn1 LIKE 'SomeValue%' -- Optional child-row filter
) AS C
-- Usually the MatchColumns are the same as the PKey columns
ON C.MatchColumn1 = M.MatchColumn1
AND C.MatchColumn2 = M.MatchColumn2
WHERE C_RowNumber <= 5 -- Optional Child "Limit Rows"
ORDER BY M.MatchColumn1
, M.MatchColumn2
, C.ChildSortCol1
, C.ChildSortCol1