Finding maximum combination of keys that share the same value

user_9592902 0
Viewed 67

Here is my table:

CREATE TABLE ABC(
 key NUMBER(5), 
 val NUMBER(5)
 );

insert into ABC (key, val) values (1,1);
insert into ABC (key, val) values (1,2);
insert into ABC (key, val) values (1,3);
insert into ABC (key, val) values (2,3);
insert into ABC (key, val) values (1,4);
insert into ABC (key, val) values (2,4); 
insert into ABC (key, val) values (2,5);
insert into ABC (key, val) values (3,5);
insert into ABC (key, val) values (1,6);
insert into ABC (key, val) values (2,6);

Desired Output: enter image description here

I want to find the maximum pairs of keys that share the same value, and list them, in the above example the maximum pairs of keys that occur in the table are (1,2) which share values (3,4,6)

oracle sql
2 Answers

Please try as below:

select ab,val from (
select rank() over( order by cnt desc) rn, ab, val ,cnt
from (
select listagg(key,',')  within group(order by val) ab,val, 
count(1) over (partition by (listagg(key,',')  within group(order by val))) cnt
from abc
group by val
having count(*) > 1))
where rn = 1;
user_129915430

You can use self join and analytical function as follows:

Select * from
(Select t.key, tt.key as key1, t.val,
        Count(distinct val) over (partition by t.key, tt.key) as cnt
  From your_table t join your_table tt
    On t.val = tt.val and t.key < tt.key)
Order by cnt desc 
fetch first 1 row with ties;

In oracle 11g, fetch clause is not supported. So you should use dense_rank as follows:

select key, key1, val from
(select key, key1, val, 
dense_rank() over (order by cnt desc) as dr  from(
Select a.key, b.key as key1, a.val,
        Count(distinct a.val) over (partition by a.key, b.key) as cnt
  From abc a join abc b
    On a.val = b.val and a.key < b.key) )
    where dr = 1;

http://sqlfiddle.com/#!4/40106f/15

user_115656290
Related