Possible to select distinct combinations?

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 something that could be fixed with a second pass...I'm not sure how that could be done in a single query since you'd be changing the order of the columns on a row-by-row basis...

Right now the pairs table is huge and it's being processed by a row-by- row PL/SQL that tries to insert on a results table and catches DUP_VAL_ON_INDEX...it's pretty slow.

Ideas? Thanks! Received on Fri Jul 20 2007 - 21:22:58 CDT