CODEWITHSUNDEEP
sqloracle-databaseselectplsql
Archimedes Trajano
Archimedes Trajano

Reputation: 41390

Pairs with no duplicates grouped together

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.

Upvotes: 0

Views: 47

Answers (1)

Mureinik
Mureinik

Reputation: 311573

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;

Upvotes: 1

Related Questions