Since that was found interesting here are some more useful ideas.
A permutation is an arrangment of items where order matters
A combination is an arrangment of items where the order does not matter
123 and 321 are two different permutations
123 and 321 are the same combination.
A combination lock is a bad name and should be a "permutation lock" because the order matters. If you dial 321 it is different than 123 and will not open.
For both combinations and permutations there are two types
1 Permuations with replacement
2 Permutations without replacement (cannot reuse values)
3 combinations with replacement
4 combinations without replacement
In these example assume the possible values (Ni) are 1,2,3,4, and you are choosing 3 (R) items at a time.
The data is stored in
tblItms
itm (a field with 1,2,3,4)
1) Permutations with replacement means I can have values 1,1,1 up to 9,9,9
There are N*N*N possible ways to choose or N^R. In this case 4^3 = 64. This is what was previously demonstrated as a Cartesian product.
Code:
SELECT A.itm AS A, B.itm AS B, C.itm AS C
FROM tblItems AS A, tblItems AS B, tblItems AS C
ORDER BY A.itm, B.itm, C.itm;
Code:
A B C
1 1 1
1 1 2
1 1 3
1 1 4
1 2 1
1 2 2
1 2 3
1 2 4
1 3 1
1 3 2
1 3 3
.....
4 4 4
2) Permuations without replacement. If you pick a number you cannot reuse it. So 1,2,1 or 1,2,2 are not allowed.
So in this case you have N ways to choose the first number, N-1 for the second, to N-R + 1
= N!/(N-R)! = 4!/1! = 24
Code:
SELECT A.itm AS A, B.itm AS B, C.itm AS C
FROM tblItems AS A, tblItems AS B, tblItems AS C
WHERE (((B.itm)<>[A].[itm] And (B.itm)<>[c].[itm]) AND ((C.itm)<>[B].[itm] And (C.itm)<>[A].[itm]))
ORDER BY A.itm, B.itm, C.itm
Code:
A B C
1 2 3
1 2 4
1 3 2
1 3 4
1 4 2
1 4 3
2 1 3
2 1 4
2 3 1
2 3 4
2 4 1
2 4 3
3 1 2
3 1 4
3 2 1
3 2 4
3 4 1
3 4 2
4 1 2
4 1 3
4 2 1
4 2 3
4 3 1
4 3 2
3) Combination without replacement. Same as above except 1,2,3 is no different then 3,2,1 no differnt than 2,1,3
There are
N!/R!(N-R)! = 4!/3!*1! = 24/6 = 4
Code:
SELECT A.itm AS A, B.itm AS B, C.itm AS C
FROM tblItems AS A, tblItems AS B, tblItems AS C
WHERE (((B.itm)>[a].[itm]) AND ((C.itm)>[b].[itm]))
ORDER BY A.itm, B.itm, C.itm;
Code:
A B C
1 2 3
1 2 4
1 3 4
2 3 4
4) Combinations with Replacement. This one is a little tricky. May be an easier way. You find this by finding all the combinations where each item is different, all combinations where you repeat 2 of the items, and all the combinations where you repeat each item and so forth.
So 1,1,2 is allowed but is is no different from 2,1,1
qrySingles (all values are equal)
Code:
SELECT A.itm AS A, B.itm AS B, C.itm AS C
FROM tblItems AS A, tblItems AS B, tblItems AS C
WHERE (((B.itm)=[a].[itm]) AND ((C.itm)=[b].[itm]))
ORDER BY A.itm, B.itm, C.itm;
qryDoubles (2 values are the same)
Code:
SELECT A.itm AS A, B.itm AS B, C.itm AS C
FROM tblItems AS A, tblItems AS B, tblItems AS C
WHERE (((B.itm)=[a].[itm]) AND ((C.itm)<>[b].[itm]))
ORDER BY A.itm, B.itm, C.itm;
Triples is the same as combinations without replacement
so
Code:
select *, "3 Triples" as Type from qrycombinationsWithoutReplacement
union select * ,"2 Doubles" from qryDoubles
UNION select *, "1 Singles" from qrySingles
ORDER BY 1, 2, 3, 4;
the number is
(N + r - 1)! /R!(n-1)! = 6!/3!*3! = 720 / 36 = 20
Code:
Type A B C
1 Singles 1 1 1
1 Singles 2 2 2
1 Singles 3 3 3
1 Singles 4 4 4
2 Doubles 1 1 2
2 Doubles 1 1 3
2 Doubles 1 1 4
2 Doubles 2 2 1
2 Doubles 2 2 3
2 Doubles 2 2 4
2 Doubles 3 3 1
2 Doubles 3 3 2
2 Doubles 3 3 4
2 Doubles 4 4 1
2 Doubles 4 4 2
2 Doubles 4 4 3
3 Triples 1 2 3
3 Triples 1 2 4
3 Triples 1 3 4
3 Triples 2 3 4
