I have a table that contains connections between specific areas. However, each connection occurs twice - once on the lead in and once on the lead out. I am trying to find each unique occurrence of a connection across the two columns, regardless of the order the records are stored in the table.

I have tried a number of queries using FIRST_VALUE, but cannot seem to get the result that I am after and my attempts at CONNECT BY PRIOR never return at all.

The data looks like this:

   TAB_NAME  CONX
1    HOME    AB1
2    AB1     HOME
3    AB1     AB2
4    AB2     AB1
5    AB3     AB2
6    AB2     AB3

I am trying to get the unique occurrence of each connection:
HOME - AB1
AB1-AB2
AB2-AB3

create table connections (tab_name varchar2(50), conx varchar2(50));
insert into connections values('HOME', 'AB1');
insert into connections values('AB1', 'HOME');
insert into connections values('AB1', 'AB2');
insert into connections values('AB2', 'AB1');
insert into connections values('AB3', 'AB2');
insert into connections values('AB2','AB3');

Any suggestions that you could provide would be great.

Thanks for your help,

Patricia

ops$tkyte%ORA11GR2> select distinct tab_name, conx
  2    from (
  3  select tab_name , conx
  4    from connections
  5  connect by nocycle prior conx  = tab_name
  6         )
  7   where tab_name < conx
  8  /

TAB_NAME CONX
-------- --------
AB1      AB2
AB1      HOME
AB2      AB3