Re: Possible to select distinct combinations?

"Aftermath Fan" <survivalist_at_gmail.com> a écrit dans le message de news: 1184984578.616166.64410_at_k79g2000hse.googlegroups.com...
| 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!
|

select distinct least(numa,numb), greatest(numa,numb) from pairs;

Regards
Michel Cadot Received on Sat Jul 21 2007 - 00:48:55 CDT