How to get SQL results as Different sets in SQL Query

Question

I have the following requirement: by using SQL IN clause with getting top 20 record. My query is

Select CatId,Name,Color 
from Table1 
where CatId in (10,11,12)

and results are like

 CatId  | Name   | Color
 ------- -------- ------
 10       Name1    Blue
 10       Name2    red
 10       Name3    Blue
 11       Name4    Blue
 10       Name5    red
 12       Name6    Blue
 11       Name7    Blue
 12       Name8    Blue
 .......

But I need results like sets sets which has CatIds in (10,11,12)

CatId  | Name   | Color
 ------- -------- ------
 10       Name1    Blue
 11       Name4    Blue
 12       Name6    Blue
 10       Name2    Red
 11       Name7    red
 12       Name8    Blue   
 ......

Is there any way in SQL query please tell me?

Answer 1

You can use a query with ROW_NUMBER:

Select CatId, Name, Color
from (
   Select CatId, Name, Color,
          row_number() over (partition by CatId order by Name) AS rn 
   from mytable 
   where CatId in (10,11,12)) as t
order by rn, Name

The above query uses ROW_NUMBER with PARTITION BY clause, so as to enumerate records withing slices of the same CatId. So, for example, records that have CatId = 10 are assigned numbers 1, 2, 3, ..., then records with CatId = 11 are similarly assigned number 1, 2, 3, ... and so not. Using these numbers we can create an ordering like the one required.