How to Use OFFSET/FETCH Not on Rows but on Groups of Rows?

Question

If I have a one-to-many table as follows:

FamilyId          UserId
1                 1
1                 2
2                 3
2                 4
3                 5

I can use offset/fetch to get a set of rows. But how can I use it to get a set of families? How can I fetch for example, the first two families (returning a total of 4 rows in this case) using offset/fetch?

Answer 1

I know this is an old question, but I just came across it and would like to suggest shorter and hopefully more elegant solution, using DISTINCT and TOP.

SELECT *
FROM yourTable
WHERE FamilyId IN
     (SELECT DISTINCT TOP (2) FamilyId FROM yourTable)

Another approach would be to use DENSE_RANK function, e.g.

SELECT FamilyId, UserId
FROM (SELECT DENSE_RANK() OVER (ORDER BY FamilyId) AS rn, FamilyId, UserId FROM yourTable) AS t
WHERE rn < 3

If for some reason, OFFSET..FETCH usage is mandatory, then the following solution is feasible, using a variable that defines the number of records to be returned:

DECLARE @intFamilyRowCnt INT 
SET @intFamilyRowCnt = 
    (
        SELECT COUNT(FamilyId) FROM yourTable WHERE FamilyId IN 
            (SELECT DISTINCT TOP (2) FamilyId FROM yourTable)
    )

SELECT * FROM yourTable
ORDER BY FamilyId
OFFSET 0 ROWS FETCH NEXT @intFamilyRowCnt ROWS ONLY

Answer 2

As mentioned in comments you need to create row number for distinct familyid then use offset/fetch to filter the groups.

Then join the filtered groups back to the main table to get the userid's in the filtered groups

SELECT a.familyid,
       userid
FROM   yourtable a
       JOIN (SELECT Row_number()OVER(ORDER BY familyid)rn,
                    familyid
             FROM   (SELECT DISTINCT FamilyId
                     FROM   yourtable)b
             ORDER BY rn  OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY
            ) c
         ON a.FamilyId = c.FamilyId 

Note: This will work from sql server 2012+