SQL Removing Subsets from the same table

user_14296673 0
Viewed 96

I've tried looking this up on similar questions but they don't seem to be equivalent.

I'm trying to remove smaller subset groups from a table no matter the order of the values in the group. So keep the larger group and remove smaller groups as long as they are subsets of the larger one.

Group   Item
1       A
1       B
1       C
1       D
2        B
2        A
3       B
3       E
3       A
4        Z
4        W
4        Y

In this example since group 1 is largest, i want to keep it. I'd also want to keep group 4 as it isn't a subset of another group. But I want to REMOVE group 2 (as it's a subset of group 1) but not remove group 3 (since it is not a full subset).

I'll also mention that the data is massive and there is no way to determine what the "item" or "group" value will be, so I can't make queries with specific values.

subset sql
1 Answers

You can get the pairs of parent and child groups as:

select t1.group as parent_group, t2.group as childgroup
from t t2 left join
     t t1
     on t1.item = t2.item and t1.group <> t2.group
group by t1.group, t2.group
having count(t1.item) = (select count(*) from t tt1 where tt1.group = t1.group);

This checks that everything in the child (t2) is in the parent (t1).

If you know that no groups are identical, then you can just delete all the child groups. If they can be identical, then change the having to:

having count(t1.item) = (select count(*) from t tt1 where tt1.group = t1.group) and
       count(*) <> count(t1.item)   -- not an exact match
user_11440350
Related