I have 3 tables:

Table 1:

Table 2: combinations are based on item_class

sample data for table 2:

Output table:

I have the following query:

select a.city, a.wh,a.code,a.priority,b.qty,b.combination
from table_1 a
left join table_2 b on a.category=b.item_class

Now, for a particular city the value of combination in the output table should go till the maximum combination (max column in table1): for example: for Bangalore in table1 it should pick all the values of cutoff, qty, combination from table2 till 20 which is max value for comb, as given in table1. How do I put a condition for the above problem?

CodePudding user response：

How about use the row_number function. partition by the city and order by the relevant columns that make your combination and pick the ones less than or equal to that row number. something like

with t as (
  select a.city, a.wh, a.code, a.priority, b.qty, b.combination, max, 
         row_number() over (
           partition by a.city 
           order by a.wh, a.code, a.priority, b.qty, b.combination
         ) as rn 
  from table_1 a 
  left join table_2 b on a.category = b.item_class
) 
select * 
from t 
where rn < = max