patternMinor
What's the best way to list groups of values that are contained in other groups
Viewed 0 times
thegroupswhatarewaycontainedthatvalueslistother
Problem
For example, starting with a table like this:
How do I get this:
I'm posting my own effort as an answer but wondering if there is some other fancy way, perhaps with analytics or a set operator I don't know about
--edit
My test data unintentionally implies that the sets always consist of consecutive integers - unfortunately this is not true with my real data.
create table t as
select 'A' as x, level as y from dual connect by level<=5
union all
select 'B' as x, level+2 as y from dual connect by level<=5
union all
select 'C' as x, level as y from dual connect by level<=3
union all
select 'D' as x, level+2 as y from dual connect by level<=3;
alter table t add primary key (x, y);
select * from t;
X Y
- -
A 1
A 2
A 3
A 4
A 5
B 3
B 4
B 5
B 6
B 7
C 1
C 2
C 3
D 3
D 4
D 5How do I get this:
SUBSET_X SUPERSET_X
-------- ----------
D A
C A
D BI'm posting my own effort as an answer but wondering if there is some other fancy way, perhaps with analytics or a set operator I don't know about
--edit
My test data unintentionally implies that the sets always consist of consecutive integers - unfortunately this is not true with my real data.
Solution
This does the trick but is not terribly fast:
select t1.x as subset_x, t2.x as superset_x
from t t1 join t t2 on(t1.y=t2.y and t1.x<>t2.x)
group by t1.x, t2.x
having count(*)=(select count(*) from t where x=t1.x);Code Snippets
select t1.x as subset_x, t2.x as superset_x
from t t1 join t t2 on(t1.y=t2.y and t1.x<>t2.x)
group by t1.x, t2.x
having count(*)=(select count(*) from t where x=t1.x);Context
StackExchange Database Administrators Q#7014, answer score: 3
Revisions (0)
No revisions yet.