How 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:
then I want the result to be
...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.
select distinct least(col1, col2), greatest (col1,col2)
Click Here to Expand Forum to Full Width