Pairs with no duplicates grouped together

Question

I have a table

ID  GROUPID  NAME
==  =======  ========
 1      100  A
 2      100  B
 3      200  C
 4      200  D
 5      300  E
 6      100  F

I would like to create a table containing the permutation pairs within a group without any pairs that are the same on both first and second that looks like this:

 PAIRID  FIRST  SECOND
 ======  =====  ======
      1      1       2
      2      1       6
      3      2       1
      4      2       6
      5      3       4
      6      4       3
      7      6       1
      8      6       2

I would like to do it in PL/SQL or straight SQL inserts if possible. I did this through Java already using a recursive function to go through the permutations.

Correct it is there so I can call it "a", "b", "c" if there are any discussions. — Archimedes Trajano, Sep 01 '17 at 19:21

Mureinik · Accepted Answer · 2017-09-01T19:03:25.193

1

You could self join the table:

SELECT   ROW_NUMBER() OVER (ORDER BY a.id, b.id) AS pairid, 
         a.id AS FIRST, b.id AS second
FROM     mytable a
JOIN     mytable b ON a.groupid = b.groupid AND a.id <> b.id
ORDER BY 1 ASC;

edited Sep 01 '17 at 19:03

answered Sep 01 '17 at 18:10

Mureinik

297,002
52
306
350

1

Almost there, but - while the OP did not say it explicitly - it looks like the pairid is assigned based on "ORDER BY first, second". So a `ROW_NUMBER()` seems necessary. – Sep 01 '17 at 19:00

Pairs with no duplicates grouped together

1 Answers1