sql server - SQL select dynamic number of records -

this weird problem describe, please bare me.

using sql server 2005, trying select number of records (dynamic) 1 table, based on table number of records needs be.

table 1 has category id , number of records want returned category.

category id  top_limit ----------------------   cat 1        1 cat 2        2 cat 3        10 

table 2 has product id, category id, , quantity:

product id  category id  quantity --------------------------------- part 1      cat 1        10   part 2      cat 1        20   part 3      cat 2        100   part 4      cat 2        100   part 5      cat 2        50   part 6      cat 3        5   

how can write query me correct "top" product records table 2 (part 2, part 3 & 4, part 6)?

try this:

;with cte (   select productid, categoryid, quantity,          [row] = row_number() over(partition categoryid order quantity desc)   table2 ) select t2.product, t2.categoryid, t2.quantity cte t2      join table1 t1 on t2.categoryid=t1.categoryid t2.row <= t1.top_limit