top 10 of records matching distinct combinations

[fredericofonseca]

hi all,

Need help with the following. I know I can do this using temp tables, but looking for another solutions if it makes it easier

I have to give a list of items id's to our testers that include all the possible combinations of item_id/category in order to make their life easier.

item_table contains a item id, and for each item there can be several prod_id's from ref_table.
ref_table id's contains 264 records, with around 30 distinct categories.

I have to output a list of id's from item_table based on the following.

up to around 20 item_id's per distinct combination of category id's,
e.g.
if 100 item_id's are linked to prod_id's with category 1, and no other category then output 20 of those items.
if 100 item_id's are linked to prod_id's with category 1 and also to prod_id's with category 2, and no other categories then output 20 of those items.
if 100 item_id's are linked to prod_id's with category 1 and also to prod_id's with category 2 and also to prod_id's with category 3, and no other categories then output 20 of those items.

hope what I am looking for is clear. will happily get back more info to help on this.



Frederico

Item_table contains around 3million rows - many records per id, all distinct (id, prod_id)
ref_table contains 264 records

item_table	
id	
prod_id	
	
ref_table	
prod_id	
category	
	
	
	
sample data	
item_table	
ID	Prod_id
a	1
a	2
a	5
b	1
b	3
b	5
b	6
c	1
c	2
c	3
c	4
c	5
d	3
e	4
f	6
g	6
h	4
j	4
	
	
ref_table	
1	10
2	20
3	10
4	20
5	30
6	40

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[LKBrwnDBA]

And:
1) Based on data you posted, what is the reult you want?
2) What have you coded so far?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[fredericofonseca]

managed to do it now. just took me awhile to figure out a way to do it

by using the sum(power(2,category)) I am able to group the item_table records into all the possible combinations available over the used ref_id/category. once this is done a simple rank/partition did the trick.
Just had this on the back of my mind to do for a few days, and had not yet figure it out till today.

select id
from   (select id, ref_cnt ,
  rank() over
  (partition by ref_cnt order by  id) as rank1
  from 
    (select f1.id, sum(power(2,category)) as ref_cnt
  from item_table f1
inner join ref_table f2
on f1.ref_id = f2.id
group by f1.id
)
order by ref_cnt
)
where rank1 < 10

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886