Oracle SQL: assignation counting up to maximum

I have the below 2 tables:

aux1:

PC	A	MAXI
PC1	A1	1
PC1	A2	2
PC2	A1	1
PC2	A2	2

aux:

VK	D	PC
VK1	8	PC1
VK2	7	PC1
VK3	6	PC2
VK4	5	PC2
VK5	4	PC1
VK6	3	PC1
VK7	2	PC2
VK8	1	PC2

I would like to obtain the following output:

VK	D	PC	A	ORDER_A_PC	ORDER_PC
VK1	8	PC1	A1	1	1
VK2	7	PC1	A2	1	2
VK3	6	PC2	A1	1	1
VK4	5	PC2	A2	1	2
VK5	4	PC1	A2	2	3
VK6	3	PC1	NA	NA	NA
VK7	2	PC2	A2	2	3
VK8	1	PC2	NA	NA	NA

The logic for the columns is:

A: ordering aux by D desc and partitioning by PC, assign A (A1 or A2) until the count over partition reaches the maxi configured in table aux1 for each PC and A.
ORDER_A_PC: ordered count over partition by A and PC until the count exceeds the maxi for that PC, A in table aux1 ORDER_PC: same as ORDER_A_PC but partitioned by just PC.

with aux (vk, pc) as (      select 'VK1', 'PC1' from dual union all      select 'VK2', 'PC1' from dual union all      select 'VK3', 'PC2' from dual union all      select 'VK4', 'PC2' from dual union all      select 'VK5', 'PC1' from dual union all      select 'VK6', 'PC1' from dual union all      select 'VK7', 'PC2' from dual union all      select 'VK8', 'PC2' from dual),  aux1 (pc, a, maxi) as (      select 'PC1', 'A1', 1 from dual union all       select 'PC1', 'A2', 2 from dual union all       select 'PC2', 'A1', 1 from dual union all       select 'PC2', 'A2', 2 from dual)  select * from aux  

https://stackoverflow.com/questions/67341793/oracle-sql-assignation-counting-up-to-maximum May 01, 2021 at 08:12AM