Another Challenge for Aaron

[Harlan Grove]

I hadn't thought of this before because it's generally not useful, but there
are infrequent but predictably recurring requests for generating
permutations. For example, given the original ordered 3-tuple {1, 2, 3}
generate

1 2 3
2 1 3
1 3 2
3 1 2
2 3 1
3 2 1

If you have means of generating arbitrary cartesian products, then you could
generate the 3-way cartesian product of this 3-tuple with itself, then
select all nodes with distinct coordinates (dunno what the terminology would
be). So generate 27 (3^3) nodes in order to sift out 6 (3!) permutations.
Already seems wasteful.

Move on to 4-tuples. The cartesian product would generate 256 (4^4) nodes in
order to sift out 24 (4!) permutations. 5-tuples: 3,125 nodes for 120
permutations. 6-tuples: 46,656 nodes for 720 permutations. 7-tuples: 823,543
nodes for 5,040 permutations. 8-tuples: 16,777,216 nodes for 40,320
permutations. Gets rather wasteful pretty quickly, no? And the query(ies)
needed to sift out the nodes with distinct coordinates wouldn't exactly run
in negligible time.

Now there are many ways to generate permutations, but the most general
involves fundamental permutations, which can be stored in a table. For
example, name the following PT.

____2______1__2__1__3__4__5__6__7__8
____6______2__2__3__1__4__5__6__7__8
____6______4__3__1__2__4__5__6__7__8
___24______6__3__2__4__1__5__6__7__8
___24_____12__3__4__1__2__5__6__7__8
___24_____18__4__2__1__3__5__6__7__8
__120_____24__4__3__2__5__1__6__7__8
__120_____48__4__3__5__1__2__6__7__8
__120_____72__4__5__2__1__3__6__7__8
__120_____96__5__3__2__1__4__6__7__8
__720____120__5__4__3__2__6__1__7__8
__720____240__5__4__3__6__1__2__7__8
__720____360__5__4__6__2__1__3__7__8
__720____480__5__6__3__2__1__4__7__8
__720____600__6__4__3__2__1__5__7__8
_5040____720__6__5__4__3__2__7__1__8
_5040___1440__6__5__4__3__7__1__2__8
_5040___2160__6__5__4__7__2__1__3__8
_5040___2880__6__5__7__3__2__1__4__8
_5040___3600__6__7__4__3__2__1__5__8
_5040___4320__7__5__4__3__2__1__6__8
40320___5040__7__6__5__4__3__2__8__1
40320__10080__7__6__5__4__3__8__1__2
40320__15120__7__6__5__4__8__2__1__3
40320__20160__7__6__5__8__3__2__1__4
40320__25200__7__6__8__4__3__2__1__5
40320__30240__7__8__5__4__3__2__1__6
40320__35280__8__6__5__4__3__2__1__7

Then given the first row of an 8-tuple in, say, A1:H1, the remaining
permutations require only the following steps.

Enter the following formula.

A2:
=INDEX($A1:$H1,LOOKUP(2,1/(MOD(ROWS(A$2:A2),INDEX(PT,0,1))=INDEX(PT,0,2)),
INDEX(PT,0,COLUMNS($A2:A2)+2)))

Press [Ctrl]+C.

Press [F5], enter A2:H40320, and press [Enter].

Press [Ctrl]+V.

That's it. So counting copying the table above into a workbook as one step,
naming it PT as another step, entering the initial 8-tuple as the 3rd step,
this requires a whole 7 steps in Excel.

How would you do it in an RDBMS? Note that *YOU* are the one claiming
databases are better at *EVERYTHING*. Prove it. Feel free to use the PT
table above it it'd help.