How to select distinct combinations?

[verdant]

Hello,

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!

-V.

[slimdave]

select distinct least(col1, col2), greatest (col1,col2)
from ...