Home > Net >  SQL to append records
SQL to append records

Time:09-27

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

See Working DB<>Fiddle

  • Related