How to select distinct combinations?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: How to select distinct combinations?

  1. #1
    Join Date
    Jul 2007
    Posts
    1

    How to select distinct combinations?

    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.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Code:
    select distinct least(col1, col2), greatest (col1,col2)
    from ...
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width