SQL: removing duplicate records - albeit a different kind

Question

Consider the following table:

TAB6 ABC ---------- ---------- - 1 2 A 2 1 A 2 3 C 3 4 D 

I believe that the entries {1,2, A} and {2, 1, A} are repeated. I need to select and create the following recordset:

  ABCABC ---------- ---------- - ---------- ---------- - 1 2 A or 2 1 A 2 3 C 2 3 C 3 4 D 3 4 D 

I tried the following queries. But to no avail.

 select t1.* from t6 t1 , t6 t2 where t1.a <> t2.b and t1.b <> t2.a and t1.rowid <> t2.rowid / ABC ---------- ---------- - 1 2 A 2 1 A 2 1 A 2 3 C 3 4 D 3 4 D 6 rows selected. 

Or even this:

  select * from t6 t1 where exists (select * from t6 t2 where t1.a <> t2.b and t1.b <> t2.a) / ABC ---------- ---------- - 1 2 A 2 1 A 2 3 C 3 4 D 

Both did not work.

The database will be Oracle 10g. Search for a clean SQL solution. Every help is appreciated.

Answer 1

Use the GREATEST () and LEAST () functions to identify common values ​​for multiple columns. Then use DISTINCT to display duplicates.

 select distinct least(a, b) as a , greatest(a, b) as b , c from t6 

This gives you the exact set of records you requested. But things get complicated if you need to include other columns from T6.

---

"But I was wondering if this would work for VARCHAR2 fields as well?"

Yes, but it will use ASCII values ​​to determine order, which does not always mean what you can expect (or desire).

"In addition, my T6 table can have tens of thousands of records."

It really is not much data under today's conditions. DISTINCT will cause a sort that should be able to fit in memory if A and B are not really long VARCHAR2 columns, but maybe even then.

If this is a query that you want to run a lot, you can create an index based on functions to satisfy it:

 create index t6_fbi on t6(least(a, b) , greatest(a, b) , c ) / 

But I would really worry if you have a performance issue with the query.

Answer 2

If the order of columns A and B is not relevant and always contains an integer, how about:

 select distinct least(a, b) as a, greatest(a, b) as b, c from t6