Problem with filtering permutations 2

[VickyC]

hi to all

I have a query that correctly produces the 720 permutations of the digits 0, 1, 2, 3, 4, 5.
Here's the SQL, followed by a sample of the output. tbl_K has one field, ID, with records 0, 1, 2, 3, 4, 5.

SELECT T_0.ID AS A, T_1.ID AS B, T_2.ID AS C, T_3.ID AS D, T_4.ID AS E, T_5.ID AS F
FROM tbl_K AS T_0, tbl_K AS T_1, tbl_K AS T_2, tbl_K AS T_3, tbl_K AS T_4, tbl_K AS T_5
WHERE ((T_0.ID<>T_1.ID) And (T_0.ID<>T_2.ID) And (T_0.ID<>T_3.ID) And (T_0.ID<>T_4.ID) And (T_0.ID<>T_5.ID)) 
  And ((T_1.ID<>T_2.ID) And (T_1.ID<>T_3.ID) And (T_1.ID<>T_4.ID) And (T_1.ID<>T_5.ID)) 
  And ((T_2.ID<>T_3.ID) And (T_2.ID<>T_4.ID) And (T_2.ID<>T_5.ID)) 
  And ((T_3.ID<>T_4.ID) And (T_3.ID<>T_5.ID)) 
  And ((T_4.ID<>T_5.ID));

The output has 720 records like...

A	B	C	D	E	F
0	1	2	3	4	5
0	1	2	3	5	4
0	1	2	4	3	5
0	1	2	4	5	3
0	1	2	5	3	4
0	1	2	5	4	3
....  etc    
5	4	3	1	2	0
5	4	3	2	0	1
5	4	3	2	1	0

Here is my problem, and I'm really lost as to how to handle it:

I need to FILTER out most of these 720 records by applying the following constraints.
Reading an output record from LEFT to RIGHT...

[tt]a) 0 must precede 1, 2, 3, 4 and 5 (for eg, record 1, 0, 3, 4, 2, 5 is NOT allowed)
b) 2 must precede 3, 4 and 5 (for eg, record 0, 1, 4, 5, 2, 3 is NOT allowed)
c) 4 must precede 5 (for eg, record 0, 2, 1, 5, 4, 3 is NOT allowed)[/tt]

An example of a valid record is ( 0, 2, 1, 4, 3, 5 ) because it meets all 3 constraints.

Thanks in advance for any pointers
Vicky C.

 

[mmogul]

Are you limited to using sql or would you consider a vba solution?

 

[MajP]

a) 0 must precede 1, 2, 3, 4 and 5 (for eg, record 1, 0, 3, 4, 2, 5 is NOT allowed)
The only way that is possible is that the the first digit is 0

AND T_0.ID = 0

b) 2 must precede 3, 4 and 5 (for eg, record 0, 1, 4, 5, 2, 3 is NOT allowed)
only way that is possible is that the second digit is a 2 or the third digit is a 2 and the first digit is a 1

AND (T_1.ID = 2 OR (T_2.ID = 2 AND T_1.ID = 1)

c) 4 must precede 5 (for eg, record 0, 2, 1, 5, 4, 3 is NOT allowed
A four cannot be in the first position, or second, or sixth.
It has to be in position 3,4,5

It can be in the third position if 5 is in the 4,5,6 position
OR
It can be in the 4th position is 5 is in the 5,6 position
OR
it can be in the 5 position if 5 is in the 6 position

Not sure if I can get the parentheses correct
AND (
(T_2.ID = 4 AND (T_3 = 5 OR T_4 = 5 or T_5 = 5)
OR
(T_3 = 4 AND (T_4 = 5 or T_5 = 5)
OR
(T_4 = 5 AND T_5 = 5)
)

 

[PHV]

MajP, I'd simplify your c) rule like this:
AND (
(T_2.ID = 4 AND 5 In (T_3.ID,T_4.ID,T_5.ID)
OR
(T_3.ID = 4 AND 5 In (T_4.ID,T_5.ID)
OR
(T_4.ID = 4 AND T_5.ID = 5)
)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

Excellent help. Thank you!
I had posted in this forum because I suspected that a VBA solution would be needed, but the SQL solution is now up and running due to your great advice.

Vicky C.