Getting all the combinations of 6 numbers from a set of specific numbers using t-sql

Question

I need to extract all the possible combinations of 6 unique numbers (in ascending order) from a set of 15 numbers.

Assuming the set of 15 numbers are as follows: 3, 5, 8, 14, 18, 20, 21, 27, 28, 30, 32, 33, 37, 39, 40, how can extract a list of all possible combinations of 6 numbers using t-sql?

I also need the output of numbers to be added into a table with columns n1, n2, n3, n4 ,n5 and n6.

I have found the following questions which relate somewhat to my problem but I am finding it hard to translate the answers to fix my specific problem:

(1) Generate all combinations in SQL

(2) How to generate all possible data combinations in SQL?

Ok... now you have an answer that works and it's your turn to answer a question. Why do you need to do this? — Jeff Moden, Jun 08 '20 at 01:35
@JeffModen I suspect this is a bin optimization or a Traveling Salesman Problem. Optimization is easy enough to do with a relatively small number of observations. However, the number of combination get astronomical real fast. For example 25/25 would generate 1.55E+25 permutations. 60/60 would generate 8.32E+81 (more atoms than in the entire universe) Fun stuff :)\ — John Cappelletti, Jun 09 '20 at 22:00
@JohnCappelletti -- Thanks and I would think either those things and a couple of more but we don't know for sure. I'm hoping that the OP can let us know the real reason for wanting to do this,. — Jeff Moden, Jun 09 '20 at 22:29

John Cappelletti · Answer 1 · 2020-06-08T16:48:07.627

Here is a option which does not allow repeat values

Note: This returns 3.6MM rows and assumes 3,5,.. is not the same as 5,3,...

Example

Declare @YourTable Table ([N] int)  
Insert Into @YourTable Values 
(3),(5),(8),(14),(18),(20),(21),(27),(28),(30),(32),(33),(37),(39),(40)

Select N1=N1.N 
      ,N2=N2.N 
      ,N3=N3.N
      ,N4=N4.N
      ,N5=N5.N
      ,N6=N6.N
 From  @YourTable N1
 Join  @YourTable N2 on N2.N<>N1.N
 Join  @YourTable N3 on N3.N<>N1.N and N3.N<>N2.N
 Join  @YourTable N4 on N4.N<>N1.N and N4.N<>N2.N and N4.N<>N3.N
 Join  @YourTable N5 on N5.N<>N1.N and N5.N<>N2.N and N5.N<>N3.N and N5.N<>N4.N
 Join  @YourTable N6 on N6.N<>N1.N and N6.N<>N2.N and N6.N<>N3.N and N6.N<>N4.N and N6.N<>N5.N

If repeats are allowed, you can either use a CROSS JOIN or remove the ON criteria

score 1 · Accepted Answer · answered Jun 07 '20 at 22:16

Assuming these are in a table, say numbers, use join:

select n1.n, n2.n, n3.n, n4.n, n5.n, n6.n
from numbers n1 join
     numbers n2
     on n1.n < n2.n join
     numbers n3
     on n2.n < n3.n join
     numbers n4
     on n3.n < n4.n join
     numbers n5
     on n4.n < n5.n join
     numbers n6
     on n5.n < n6.n;

The join conditions specify that numbers are used only once, which is typical in this type of problem, and that a given combination only occurs once -- in order from smallest to largest.

Getting all the combinations of 6 numbers from a set of specific numbers using t-sql

2 Answers2