SQL - remove duplicate tuples, even if values are out of order

Question

Let's say I have a table like this:

name_1 name_2 value
-------------------
john   alex    6
alex   john    6
bob    rick    7
rick   bob     7

I want to get rid of the duplicates so I'm left with this:

name_1 name_2 value
-------------------
john   alex    6
rick   bob     7

Does distinct work? And if so, how would I apply it?

EDIT:

I'm not concerned about the order of the names in the final table. I am looking for name pairs. So I am treating john alex the same as alex john. Therefore, I want to get rid of those "duplicates"

Answer 1

Here's one option using least with greatest and distinct:

select distinct least(name_1, name_2) name_1,
       greatest(name_1, name_2) name_2,
       value
from yourtable

• SQL Fiddle Demo

Answer 2

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table table_name (name1, name2, value) AS
SELECT 'john', 'alex', 6 FROM DUAL UNION ALL
SELECT 'alex', 'john', 6 FROM DUAL UNION ALL
SELECT 'bob',  'rick', 7 FROM DUAL UNION ALL
SELECT 'rick', 'bob',  7 FROM DUAL UNION ALL
SELECT 'alice','carol',7 FROM DUAL UNION ALL
SELECT 'carol','alice',7 FROM DUAL UNION ALL
SELECT 'david','david',5 FROM DUAL;

Query 1:

SELECT name1,
       name2,
       value
FROM   (
  SELECT t.*,
         ROW_NUMBER()
           OVER ( PARTITION BY LEAST( NAME1, NAME2 ),
                               GREATEST( NAME1, NAME2 ),
                               VALUE
                  ORDER BY ROWNUM ) AS RN
  FROM   table_name t
)
WHERE  RN = 1

Results:

| NAME1 | NAME2 | VALUE |
|-------|-------|-------|
|  john |  alex |     6 |
| alice | carol |     7 |
|   bob |  rick |     7 |
| david | david |     5 |

Deleting Duplicates:

DELETE FROM table_name
WHERE ROWID IN (
  SELECT rid
  FROM   (
    SELECT ROWID AS rid,
           ROW_NUMBER()
             OVER ( PARTITION BY LEAST( name1, name2 ),
                                 GREATEST( name1, name2 ),
                                 VALUE
                    ORDER BY ROWNUM ) AS rn
    FROM   table_name
  )
  WHERE rn > 1
);

Query 1:

SELECT * FROM table_name

Results:

| NAME1 | NAME2 | VALUE |
|-------|-------|-------|
|  john |  alex |     6 |
|   bob |  rick |     7 |
| alice | carol |     7 |
| david | david |     5 |