Can i get the rows that isn't repeated inverted in my table?
I have 开发者_高级运维a table:
1|5 2|4 3|3 4|2 5|1 (the actual table is a lot of names and what I want is to just get one set of each combination) is there a way to get just the rows that are not repeated inverted at the end? I just want the: 1|5 2|4 3|3 rows.. can i do this in sql?Something like:
select distinct(case when x<y then x||'|'||y else y||'|'||x end) from table;
Should work on Oracle, in different db use equivalent of case.
Test:
select distinct(case when x<y then x||'|'||y else y||'|'||x end) from
(select 1 x,2 y from dual
union
select 1 x,2 y from dual
union
select 1 x,3 y from dual
union
select 3 x,2 y from dual
union
select 2 x,1 y from dual
);
Returns: 1|2 1|3 2|3
Would this solve your problem:
select *
from MyTable
where ColA <= ColB
Edit: Ok, if you have cases like 20, 5 then you could use this:
with allpairs as (
select ColA, ColB
from MyTable
where ColA <= ColB
union all
select ColB, ColA
from MyTable
where ColB < ColA
)
select distinct *
from allpairs
精彩评论