CODEWITHSUNDEEP
oracle-databaseselectunique
Davees John Baclay
Davees John Baclay

Reputation: 89

Oracle Select unique on multiple column

How can I achieve this to Select to one row only dynamically since the objective is to get the uniqueness even on multiple columns

enter image description here

Upvotes: 0

Views: 357

Answers (3)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

If I correctly understand words: objective is to get the uniqueness even on multiple columns, number of columns may vary, table can contain 2, 3 or more columns.

In this case you have several options, for example you can unpivot values, sort, pivot and take unique values. The exact code depends on Oracle version. Second option is listagg(), but it has limited length and you should use separators not appearing in values.

Another option is to compare data as collections. Here I used dbms_debug_vc2coll which is simple table of varchars. Multiset except does main job:

with t as (select rownum rn, col1, col2, col3,
                  sys.dbms_debug_vc2coll(col1, col2, col3) as coll 
             from test )
select col1, col2, col3 from t a
  where not exists (
    select 1 from t b where b.rn < a.rn and a.coll multiset except b.coll is empty )

dbfiddle with 3-column table, nulls and different test cases

Upvotes: 0

Popeye
Popeye

Reputation: 35900

The best solution is to use UNION

select colA from your_table
union
select colB from your_table;

Update:

If you want to find the duplicate then use the EXISTS as follows:

SELECT COLA, COLB FROM YOUR_TABLE T1
WHERE EXISTS (SELECT 1 FROM YOUR_tABLE T2
                       WHERE T2.COLA = T1.COLB OR T2.COLB = T1.COLA)

Upvotes: 0

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

select distinct
  coalesce(least(ColA, ColB),cola,colb) A1, greatest(ColA, ColB) B1
from T

Upvotes: 1

Related Questions