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

Thread: How to select distinct combinations?

  1. #1
    Join Date
    Jul 2007

    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:

    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's pretty slow.

    Ideas? Thanks!


  2. #2
    Join Date
    Aug 2002
    Colorado Springs
    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