CODEWITHSUNDEEP
sql
m3div0
m3div0

Reputation: 1596

Select all items from the top N categories

can somebody please suggest how to write sql query for MS sql server to select all items from the top N categories from the 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 say that I want to select all items from the top 2 categories, therefore I expect to get something like this

|idItem|idCategory
------------------
|1     |1
|2     |1
|4     |2

I have tried to join the tables, but then I don't know the exact number of items.

Thanks

EDIT: I just got an idea to join the table of items with the select top(N) idCategory FROM categories group by idCategory I hope it will work.

Upvotes: 0

Views: 124

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13248

If you DO NOT want to keep ties:

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

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

If you DO want to keep ties:

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

Fiddle: http://sqlfiddle.com/#!6/3bebb/8/0 (notice how categories 2 and 3 both come back because they are each tied; they both have exactly one item)

Your expected output indicates you don't care about ties, but I figured I would point it out just in case you actually do.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271161

If by "top category" you mean the ones with the most items, you can use row_number() along with aggregation:

select iditem, id
from (select idcategory, count(*) as cnt, row_number() over (order by count(*) desc) as seqnum
      from items
      group by idcategory
     ) c join
     items i
     on c.idcategory = i.idcategory
where segnum <= 1;

Upvotes: 0

Related Questions