jongusmoe
jongusmoe

Reputation: 676

Removing "duplicate" groups in SQL

I'm trying to get rid of duplicate groups in SQL, but the problem is they're not really duplicates.

I have a table like this:

a  |  b

0  |  1
2  |  3

however, the table also adds B, A back into it so I end up with this as the final table:

a  |  b

0  |  1
2  |  3
--------
1  |  0
3  |  2

What I'm trying to do is return the distinct pairs (the first table), and I'm having issues with it. Any hints would be much appreciated!

Upvotes: 0

Views: 96

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Try this:

select distinct (case when a < b then a else b end) as First,
       (case when a < b then b else a end) as Second
from t

If you are using Oracle (which is the assumption with SQLPlus), you can simplify this to:

select distinct least(a, b), greatest(a, b)
from t

Upvotes: 2

Related Questions