sql - Select all items from the top N categories -

can please suggest how write sql query ms sql server select items top n categories following scenario?

table of categories

|idctegory|strname ------------------ |1        |cat 1 |2        |cat 2 |3        |cat 3 |4        |cat 4 

table of items

|iditem|idcategory ------------------ |1     |1 |2     |1 |3     |3 |4     |2 

let's want select items top 2 categories, therefore expect this

|iditem|idcategory ------------------ |1     |1 |2     |1 |4     |2 

i have tried join tables, don't know exact number of items.

thanks

edit: got idea join table of items select top(n) idcategory categories group idcategory hope work.

if not want keep ties:

with tops  (select top 2 i.idcategory, count(*) num_items     items    group i.idcategory    order num_items desc) select i.* items join tops t on i.idcategory = t.idcategory 

fiddle: http://sqlfiddle.com/#!6/3bebb/7/0

if want keep ties:

with tops  (select top 2 ties i.idcategory, count(*) num_items     items    group i.idcategory    order num_items desc) select i.* items join tops t on i.idcategory = t.idcategory 

fiddle: http://sqlfiddle.com/#!6/3bebb/8/0 (notice how categories 2 , 3 both come because each tied; both have 1 item)

your expected output indicates don't care ties, figured point out in case do.