Insert unique foreign key for each unique value of primary key

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Visitor

Insert unique foreign key for each unique value of primary key

I have a table with five columns as below,

week_id :- time grain, integer

test_prod_id :- primary key, integer

test_prod_order_id :- combined with test_prod_id is unique, integer

control_prod_id :- foreign key, where one control_prod_id could have multiple records for different test_prod_ids based on multiple matching criteria defined in staging tables

rank_of_variance:- this rank is partitioned by test_prod_id and ordered by a combination of metrics in ascending order

 

My table in current state is a cross-join, where for each week_id & test_prod_id combination, I have got multiple control_prod_ids, sorted by rank_of_variance.

My problem is that these control_prod_ids have multiple entries for different test_prod_ids and I want to create another table with unique combination of week_id, test_prod_id, test_prod_order_id, control_prod_id where every control_prod_id is mapped to only one test_prod_id

for eg,

week_id, test_prod_id, test_prod_order_id, control_prod_id, rank_of_variance

1,t1, 12345, c1, 1

1, t1, 12345, c2, 2

1, t1, 12345, c3, 3

1, t2, 31245, c4, 1

1, t2, 31245, c3, 2

1, t3, 41235, c5, 1

1, t3, 41235, c6, 2

1, t3, 41235, c3, 3

1, t4, 32145, c4, 1

1, t4, 32145, c2, 2

I want to be able to insert into a set table, unique mappings of test_prod_id and control_prod_id for each week_id in ascending order of rank_of_variance

1 REPLY
Senior Apprentice

Re: Insert unique foreign key for each unique value of primary key

I don't get what you actually want (is the example the source data or the expected result?), but it sounds like a ROW_NUMBER:

QUALIFY
   ROW_NUMBER()
   OVER(PARTITION BY control_prod_id
        ORDER BY ???) = 1