A database of all combinations of 39 numbers taken 5 at a time, total combinations = 575757

Question

I want to create a numerical database of all combinations of 39 integers(1 Through 39) taken 5 at a time. I know the total possible combinations = 575757. This would be the size of the database.

I would then like to query the database for various criteria, e.g. number of combinations that are made up of all even numbers, all odd numbers, 1 even, 1 odd, 2 even, 2 odd, etc, and then query those results for other criteria such as restricted sums of all integers, and range distribution.

How would I do this? Obviously this is a Lottery oriented idea and I expect nothing but ridicule and disdain.

score 4 · Answer 1 · answered Sep 09 '14 at 21:23

Ridicule and disdain aside, the following query would return all possible permutations of your integers.

SELECT n1.i, n2.i, n3.i, n4.i, n5.i
FROM numbers AS n1
INNER JOIN numbers AS n2 ON n1.i<n2.i
INNER JOIN numbers AS n3 ON n2.i<n3.i
INNER JOIN numbers AS n4 ON n3.i<n4.i
INNER JOIN numbers AS n5 ON n4.i<n5.i

.. given that the table or common table expression numbers contains 39 unique records with 1<=i<=39.

You could add different filters for your purposes, like how many odd numbers:

WHERE (CASE WHEN n1.i%2=1 THEN 1 ELSE 0 END)+
      (CASE WHEN n2.i%2=1 THEN 1 ELSE 0 END)+
      (CASE WHEN n3.i%2=1 THEN 1 ELSE 0 END)+
      (CASE WHEN n4.i%2=1 THEN 1 ELSE 0 END)+
      (CASE WHEN n5.i%2=1 THEN 1 ELSE 0 END)>howmanyoddnums

.. or even numbers (WHEN nx.i%2=0 THEN ...). And because the numbers are "ordered" with the < in the join, n1.i will always be the lowest and n5.i the highest.

Any more advanced features (or tuning for that matter) will come down to what platform and dialect of SQL you're using.

score 1 · Answer 2 · edited Apr 13 '17 at 12:42

Use the query @Daniel supplied to get combinations without repetitions. In Postgres, you can use generate_series() to generate the base table on the fly (typically faster than using a table of numbers). In other databases, you would create a table with one column, fill it with sequential numbers and base the query on it instead.

WITH t(a) AS (SELECT * FROM generate_series (1,6))
SELECT t1.a AS a1
     , t2.a AS a2
     , t3.a AS a3
     , t4.a AS a4
     , t5.a AS a5
FROM   t AS t1
JOIN   t AS t2 ON t2.a < t1.a
JOIN   t AS t3 ON t3.a < t2.a
JOIN   t AS t4 ON t4.a < t3.a
JOIN   t AS t5 ON t5.a < t4.a

SQL Fiddle.

Basic problems like you describe are probably best solved with combinatorics. You need the binomial coefficient ("from n choose k").

Number of rows with all odd numbers (there are 19):

select (19!) / ((14!) * (5!)) = 11628

Number of rows with all even numbers:

select (20!) / ((15!) * (5!)) = 15504

1 event, 4 odd:

select 19 * ((20!) / ((16!) * (4!))) = 92055

Etc.
But I guess a table can be useful to play with conditions and verify results.

A database of all combinations of 39 numbers taken 5 at a time, total combinations = 575757

2 Answers2