T-SQL: retrieve n times a specific number of rows where n is defined in an "IN" clause

Question

How can this query produce 20 results instead of 10?

SELECT TOP 10 colA, colB, colC
FROM table
WHERE id in (1, 2)
ORDER BY colA, colB

I would like to see 10 results for id equal to 1 and another 10 for id equal to 2. Do I have to use a cursor?

Answer 1

This way you do not need to care about how many ids are in the IN clause.

;WITH MyCTE AS
(
    SELECT colA, 
           colB, 
           colC,
           ROW_NUMBER() OVER(PARTITION BY id ORDER BY colA, colB) AS rn
    FROM   table 
    WHERE id in (1, 2) 
)
SELECT *
FROM   MyCTE 
WHERE  rn<= 10
ORDER BY colA, colB