开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜