sql - Finding maximum combination of keys that share the same value

Question

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:

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)

Answer 1

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