Skip to content
Advertisement

SQL to append records

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

Advertisement

Answer

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

See Working DB<>Fiddle

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement