OraQA

The following is an interesting puzzle posted by Jsoftware: Find permutations such that adjacent numbers form a base-10 number are not divisible by 3, 4, or 5.

SQL solution:

SQL> set timing on;
SQL> variable input  number
SQL> exec :input  := 6;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

SELECT DISTINCT (to_number(str) ) num, count(*) OVER ( ) cnt
  FROM (SELECT str
	FROM (SELECT REPLACE(sys_connect_by_path( n,',' ),',') str
               FROM (SELECT LEVEL n
		               FROM dual CONNECT BY LEVEL <= :input) YourTable
               CONNECT BY NOCYCLE n != PRIOR n
               AND mod(to_number((PRIOR n || n)) , 3) !=0
               AND mod(to_number((PRIOR n || n)) , 4) !=0
               AND mod(to_number((PRIOR n || n)) , 5) !=0
             )
       )
WHERE length(str) = :input;	

       NUM        CNT
---------- ----------
    531462          1                                                           

Elapsed: 00:00:00.16

SQL> exec :input  := 7;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15
SQL> /

       NUM        CNT
---------- ----------
   5317462          2
   5371462          2                                                           

Elapsed: 00:00:00.47

SQL> exec :input  := 8;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15
SQL> /

       NUM        CNT
---------- ----------
  53826147         30
  53826174         30
  53826714         30
  53826741         30
  58231467         30
  58231746         30
  58234617         30
  58234671         30
  58237146         30
  58237461         30
  58261347         30
  58261374         30
  58261437         30
  58261473         30
  58261734         30
  58261743         30
  58267134         30
  58267143         30
  58267314         30
  58267341         30
  58267413         30
  58267431         30
  58317462         30
  58371462         30
  58623147         30
  58623174         30
  58623417         30
  58623471         30
  58623714         30
  58623741         30                                                           

30 rows selected.

Elapsed: 00:00:00.03                                                       

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.