Minimizing the number of parent rows for a distinct column in a child table

Question

I have three tables with the following format

A        B       C
-----    -----   -----
aKey     bKey    fk_c_b
         fk_b_a  c_enum
                 c_value

aKey and bKey are the primary key for each table, C does not have a primary key. fk_b_a and C.fk_c_b are the foreign keys that points to the parent table. c_enum is a enum of 87 different values. C.c_value is float that can be any value

The relationships between the tables are:

• (A.aKey) 0..1 --- * (B.fk_b_a)
• (B.bKey) 0..1 --- * (C.fk_c_b)

There are:

• 57,668 rows in A
• 650,768 rows in B
• 34,734 rows in C

I need to display a distinct list of all c_enum values and their parents, however If two rows in C share the same fk_c_b and c_value values but different c_enum values I can use not those two rows in the distinct list.

select aKey, bKey, c_enum, c_value, newid() as id 
into #myResultSet
from A
inner join B on fk_b_a = aKey
inner join C as temp1 on fk_c_b = bKey
where c_value not in (select c_value from C as temp2 
                         where temp1.c_enum <> temp2.c_enum 
                            and temp1.fk_c_b = temp2.fk_c_b 
                            and temp1.c_value = temp2.c_value)

--This is the step that I do not know how to optimize
delete #myResultSet where id <> (select top 1 id from #myResultSet as t1 where #myResultSet.c_enum = t1.c_enum)

Also in that process I need to minimize the the distinct count of aKey and bKey. So If I did

select count(distinct aKey), count(distinct bKey), count(*) from #myResultSet

the result would have the minimum values possible. The optimal result is 1, 1, 87 and the worst case would be 87, 87, 87. My current method does not attempt to get minimal values (my results are 14, 17, 87 which is not bad, but I think that is just luck with this dataset, running on a different dataset may return worse results) and I am having a lot of trouble figuring out how to do it the right way.

What would be the correct way to solve this problem? My current method is not optimal all and I have been having a lot of trouble figuring out what I need to do to build #myResultSet with minimal values for count(distinct aKey) and count(distinct bKey).

Answer 1

I think i found the solution. On the delete step I put the these two order by clauses in

delete #myResultSet where id <> 
    (
    select top 1 id 
    from #myResultSet t1 
    where #myResultSet.c_enum = t1.c_enum
    order by count(aKey) over(partition by aKey), 
             count(bKey) over(partition by aKey, bKey)
    )

This gives me 10,13,87 which I think is the optimal solution I can get with my data set.

If I am wrong or there is a better way to do this I am welcome to more answers.