skinnyWill skinnyWill - 2 months ago 14
SQL Question

how to iterate through result set and return N rows per group in result set

So far I have a cte query that returns a result that i would like to iterate over the result set and select 2 records per group based on type column ie...
I could return 2 rows for type 20, return 2 rows for type 21, return 2 rows of record for type 22 ect... I've tried a few functions on here but there always returning 2 records instead of 2 records per above mentioned . Thanks

Acct Cde type AcctNbr
123456 50 20 2345678
123457 50 20 2345678
123458 50 20 2345678
123459 50 20 2345678
123460 50 21 2345678
123461 50 21 2345678
123462 50 21 2345678
123463 50 21 2345678
123464 50 22 2345678
123465 50 22 2345678
123466 50 22 2345678
123467 50 22 2345678
123468 50 23 2345678


I'm trying to return at least 2 rows per type column . I've used a cte with
main query and

Answer

You don't say how you'd choose which two rows you want, so I've arbitrarily chosen to order by Acct. Then use the ROW_NUMBER function to number the rows by type.

WTIH YourFirstCTE AS (
    -- Appropriate code goes here
),
cteRowNum AS (
    SELECT Acct, Cde, type, AcctNbr,
           ROW_NUMBER() OVER(PARTITION BY type ORDER BY Acct) AS RowNum
        FROM YourFirstCTE
)
SELECT Acct, Cde, type, AcctNbr
    FROM cteRowNum
    WHERE RowNum <= 2;