Filter SQL query by a unique set of column values, regardless of their order

Question

I have a table in Oracle containing two columns that I'd like to query for records containing a unique combination of values, regardless of the order of those values. For example, if I have the following table:

create table RELATIONSHIPS (
    PERSON_1 number not null,
    PERSON_2 number not null,
    RELATIONSHIP  number not null,
    constraint PK_RELATIONSHIPS
        primary key (PERSON_1, PERSON_2)
);

I'd like to query for all unique relationships. So if I have a record PERSON_1 = John and PERSON_2 = Jill, I don't want to see another record where PERSON_1 = Jill and PERSON_2 = John.

Is there an easy way to do this?

Answer 1

select distinct
case when PERSON_1>=PERSON_2 then PERSON_1 ELSE PERSON_2 END person_a,
case when PERSON_1>=PERSON_2 then PERSON_2 ELSE PERSON_1 END person_b
FROM RELATIONSHIPS;

Answer 2

I think something like this should do the trick:

select * from RELATIONSHIPS group by PERSON_1, PERSON_2

Answer 3

Is the relationship always there in both directions? i.e. if John and Jill are related, then is there always a {John,Jill} and {Jill,John} ? If so, just limit to those where Person_1 < Person_2 and take the distinct set.

Answer 4

You should create a constraint on your Relationships table so that the numeric person_1 value must be less than the numeric person_2 value.

create table RELATIONSHIPS (
    PERSON_1 number not null,
    PERSON_2 number not null,
    RELATIONSHIP  number not null,
    constraint PK_RELATIONSHIPS
        primary key (PERSON_1, PERSON_2),
    constraint UNIQ_RELATIONSHIPS
        CHECK (PERSON_1 < PERSON_2)
);

That way you can be sure that (2,1) can never be inserted -- it would have to be (1,2). Then your PRIMARY KEY constraint will prevent duplicates.

PS: I see Marc Gravell has answered more quickly than I have, with a similar solution.

Answer 5

I think KM almost got it right, I added concat.

SELECT DISTINCT *
    FROM (SELECT DISTINCT concat(Person_1,Person_2) FROM RELATIONSHIPS
          UNION 
          SELECT DISTINCT concat(Person_2, Person_1) FROM RELATIONSHIPS
         ) dt

Answer 6

it's kludgy as heck, but it'd at least tell you what unique combinations you have, just not in a real handy way...

select distinct(case when person_1 <= person_2 then person_1||'|'||person_2 else person_2||'|'||person_1 end)
from relationships;

Answer 7

Untested:

select least(person_1,person_2)
     , greatest(person_1,person_2)
  from relationships
 group by least(person_1,person_2)
     , greatest(person_1,person_2)

To prevent such double entries, you can add a unique index, using the same idea (tested!):

SQL> create table relationships
  2  ( person_1 number not null
  3  , person_2 number not null
  4  , relationship number not null
  5  , constraint pk_relationships primary key (person_1, person_2)
  6  )
  7  /

Table created.

SQL> create unique index ui_relationships on relationships(least(person_1,person_2),greatest(person_1,person_2))
  2  /

Index created.

SQL> insert into relationships values (1,2,0)
  2  /

1 row created.

SQL> insert into relationships values (1,3,0)
  2  /

1 row created.

SQL> insert into relationships values (2,1,0)
  2  /
insert into relationships values (2,1,0)
*
ERROR at line 1:
ORA-00001: unique constraint (RWIJK.UI_RELATIONSHIPS) violated

Regards, Rob.

Answer 8

There's some uncertainty as to whether you want to prevent duplicates from being inserted into the database. You might just want to fetch unique pairs, while preserving the duplicates.

So here's an alternative solution for the latter case, querying unique pairs even if duplicates exist:

SELECT r1.*
FROM Relationships r1
LEFT OUTER JOIN Relationships r2
  ON (r1.person_1 = r2.person_2 AND r1.person_2 = r2.person_1)
WHERE r1.person_1 < r1.person_2
  OR  r2.person_1 IS NULL;

So if there is a matching row with the id's reversed, there's a rule for which one the query should prefer (the one with id's in numerical order).

If there is no matching row, then r2 will be NULL (this is the way outer join works), so just use whatever is found in r1 in that case.

No need to use GROUP BY or DISTINCT, because there can only be zero or one matching rows.

Trying this in MySQL, I get the following optimization plan:

+----+-------------+-------+--------+---------------+---------+---------+-----------------------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                               | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------------+------+--------------------------+
|  1 | SIMPLE      | r1    | ALL    | NULL          | NULL    | NULL    | NULL                              |    2 |                          | 
|  1 | SIMPLE      | r2    | eq_ref | PRIMARY       | PRIMARY | 8       | test.r1.person_2,test.r1.person_1 |    1 | Using where; Using index | 
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------------+------+--------------------------+

This seems to be a reasonably good use of indexes.

Answer 9

Possibly the simplest solution (that does not require alteration of data structure or creation of triggers) is to create a set of results without the duplicate entries, and add one of the duplicate entries to that set.

would look something like:

 select * from relationships where rowid not in 
    (select a.rowid from  relationships a,relationships b 
       where a.person_1=b.person_2 and a.person_2=b.person_1)
union all
 select * from relationships where rowid in 
    (select a.rowid from  relationships a,relationships b where 
       a.person_1=b.person_2 and a.person_2=b.person_1 and a.person_1>a.person_2)

But usually I never create a table without a one-column primary key.

Answer 10

You could just,

with rel as (
select *,
       row_number() over (partition by least(person_1,person_2), 
                                       greatest(person_1,person_2)) as rn
  from relationships
       )
select *
  from rel
 where rn = 1;