How can I achieve this to Select to one row only dynamically since the objective is to get the uniqueness even on multiple columns
3 Answers
select distinct
coalesce(least(ColA, ColB),cola,colb) A1, greatest(ColA, ColB) B1
from T
8 Comments
Davees John Baclay
What if you have a multiple rows and you just want to get the unique values
Popeye
This solution will not work if one of the column value is null as least and greatest will give you null if one of the column value is null
Sayan Malakshinov
@Tejash that's pretty easy - just add coalesce. I've updated my example
Sayan Malakshinov
@DaveesJohnBaclay my query does exactly what you are asking for. It returns unique pairs. Do you need anithing else?
Popeye
You need to add coalesce on greatest too.
|
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)
9 Comments
Davees John Baclay
I need to return two columns, just to treat that the 2 rows are equal
Popeye
Updated answer to give duplicate records
Sayan Malakshinov
(T2.COLA = T1.COLA OR T2.COLA = T1.COLB) ? "OR"? You solution for duplicates considers (123,456) and (123,999) as duplicates
Sayan Malakshinov
Still wrong: check again, now with (123,456) and (999,123)
Sayan Malakshinov
it should be simple SELECT COLA, COLB FROM YOUR_TABLE T1 WHERE EXISTS (SELECT 1 FROM YOUR_tABLE T2 WHERE T2.COLA = T1.COLB and T2.COLB = T1.COLA)
|
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
