Dynamic Top N by group

[RJL1]

Hello

I have a new project. I have a table with customer number and a numeric value that represents the number of record I need to retrieve. For example

Customer      Records
042           10
317           25

I want to be able to run my query against my item master table and get 10 unique items for customer 042 and 25 unique items for customer 317. If the values for the record were the same no problem but I am not sure how to approach the variable nature of the record count

Thanks in advance.
Let me know if I need to detail this further

 

[fredericofonseca]

just one approach - code will not work as is but you should get the idea

select *
from (select customer
, row_number() over(partition by customer
) as rownum
from mycustomer
) t
inner join mymaster t1
on t1.customer = t.customer
and t.rownum <= t.records_to_read

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886