Re: Possible to select distinct combinations?

Aftermath Fan (survivalist_at_gmail.com) wrote:
: Is it possible to select distinct combinations?

: I have a table that consists of pairs of numbers. e.g.,

: create table pairs ( numa number, numb number );

: What I want to do is select all unique combinations of pairs. If I
: have these values:

: 1, 3
: 3, 1
: 2, 4

: then I want the result to be

: 1, 3
: 2, 4

: ...which does not include "3,1" because it's a "duplicate" of "1,3".
: Hey, I don't write these apps ;-)

: Ideally, the lower number would always come first, but that is

select distinct __MIN__(numa,numb) , __MAX__(numa,numb) from pairs;

__MIN__ is something you write, one possibility is using decode (my example is untested, but I know the general idea is correct)

__MIN()__ implemented as

        DECODE( SIGN(numa-numb) ,-1, numa,numb)

__MAX()__ similarly

	DECODE( SIGN(numa-numb) ,-1, numb,numa)
                                        ^    ^