CODEWITHSUNDEEP
sql
dpoedq
dpoedq

Reputation: 3

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?

Upvotes: 0

Views: 78

Answers (2)

srgerg
srgerg

Reputation: 19329

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

Upvotes: 1

ajuc
ajuc

Reputation: 592

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

Upvotes: 3

Related Questions