SQL query for all combinations without repetition

Question

I need a function for calculating combinations without repetition from a list.

Example: I have a list of names e.g.

Tom
Bob
Paul
Tina

Now I need a list of 3 names out of the 4 without having a name twice and without having the 3 names twice because of ordering. The correct result would be

Tom,Bob,Paul
Tom,Paul,Tina
Tom,Bob,Tina
Bob,Paul,Tina

Of course this also should work quite fast with 14 out of 19 or similar.

Answer 1

You can use regular joins:

SQL> WITH DATA AS
  2   (SELECT 'Tom' NAME
  3      FROM dual
  4    UNION ALL
  5    SELECT 'Bob' NAME
  6      FROM dual
  7    UNION ALL
  8    SELECT 'Paul' NAME
  9      FROM dual
 10    UNION ALL
 11    SELECT 'Tina' NAME
 12      FROM dual)
 13  SELECT *
 14    FROM DATA d1
 15    JOIN DATA d2 ON d1.name > d2.name
 16    JOIN DATA d3 ON d2.name > d3.name;

NAME NAME NAME
---- ---- ----
Tom  Tina Bob
Tom  Tina Paul
Tom  Paul Bob
Tina Paul Bob