I have two tables tbl1 and tbl2. Consider tbl2 as the main set and tbl1 has been derived from other sources but will essentially now be a subset of tbl2.
tbl1
| cd | productcd | type |
|---|---|---|
| 1 | 1 | A |
| 1 | 2 | AB |
| 1 | 3 | A |
| 2 | 3 | AB |
| 2 | 4 | AC |
| 3 | 1 | A |
tbl2
| cd | productcd | type | priority |
|---|---|---|---|
| 1 | 1 | A | 1 |
| 1 | 2 | AB | 2 |
| 1 | 3 | A | 3 |
| 1 | 4 | AB | 4 |
| 1 | 5 | AC | 7 |
| 2 | 1 | A | 3 |
| 2 | 3 | AB | 4 |
| 2 | 4 | AC | 8 |
| 2 | 7 | HV | 10 |
| 3 | 1 | A | 2 |
| 3 | 2 | AC | 3 |
| 3 | 7 | BC | 5 |
| 3 | 4 | E | 9 |
| 3 | 5 | T | 11 |
How do I retrieve for each group of CD limit each group to only 4 records? So the final o/p has to be all the records of tbl1 and missing records ( max limit of 4) will be populated from tbl2
Final o/p being
| cd | productcd | type |
|---|---|---|
| 1 | 1 | A |
| 1 | 2 | AB |
| 1 | 3 | A |
| 1 | 4 | AB |
| 2 | 3 | AB |
| 2 | 4 | AC |
| 2 | 1 | A |
| 2 | 7 | HV |
| 3 | 1 | A |
| 3 | 2 | AC |
| 3 | 7 | BC |
| 3 | 4 | E |
CodePudding user response：
Tbl1 as a priority source
select cd, productcd, type
from (
select *,
row_number() over(partition by cd order by src, productcd) rn
from(
select 1 src, cd, productcd, type
from tbl1
union all
select 2 src, cd, productcd, type
from tbl2
) u
) t
where src = 1 or rn <=4
order by cd, productcd
CodePudding user response：
If I understand correctly you can use row_number to select and order the required rows for each cd and use exists to prioritise rows from tbl1
with t as (
select *,
Row_Number() over(
partition by cd
order by
case when exists (
select * from tbl1 where tbl1.cd=tbl2.cd and tbl1.productcd=tbl2.productcd
) then 0 else 1
end, priority) rn
from tbl2
)
select cd, productcd, type
from t
where rn<=4