OraQA

The following is an interesting puzzle posted on Usenet rec-puzzles.org archive:

What 6 digit number, with 6 different digits, when multiplied by all integers up to 6, circulates its digits through all 6 possible positions, as follows:

ABCDEF * 1 = ABCDEF
ABCDEF * 3 = BCDEFA
ABCDEF * 2 = CDEFAB
ABCDEF * 6 = DEFABC
ABCDEF * 4 = EFABCD
ABCDEF * 5 = FABCDE

————————–SQL Solution——————————

SELECT answer FROM
(SELECT extractvalue(column_value,'/X') answer
FROM
(SELECT XML
 FROM
 (SELECT xmlagg(xmlelement(x, str||'*'||l||' ='||to_char(str * l)) ORDER BY l) XML
 ,xmlagg(xmlelement(x,sub) ORDER BY sub).getstringval()  xml_sub
 ,xmlagg(xmlelement(x,to_char(str * l)) ORDER BY to_char(str * l)).getstringval() xml_mult
  FROM
  (SELECT str,l, substr(str,l)||substr(str,0,l-1) sub
    FROM
   (SELECT str,l, count(*) OVER (PARTITION BY str) cnt
      FROM
       (SELECT REPLACE(sys_connect_by_path(n,',' ),',') str
       FROM (SELECT LEVEL n FROM dual CONNECT BY LEVEL <= 9)
       WHERE LEVEL = 6
       CONNECT BY NOCYCLE n != PRIOR n
       AND LEVEL <=6
       AND CASE WHEN LEVEL = 2 AND length(to_char(to_number(PRIOR n||n) * 6))<3
                THEN 1
         ELSE 1 END = 1
       AND CASE WHEN LEVEL = 3
          AND length(to_char(to_number(CONNECT_BY_ROOT n||PRIOR n||n) * 6))<4
                THEN 1
         ELSE 1 END = 1
   ), (SELECT LEVEL l FROM dual CONNECT BY LEVEL <= 6)
  WHERE translate( str,'X'||str * l,'X') IS NULL
 )
 WHERE cnt = 6
)
GROUP BY str
)
WHERE xml_mult = xml_sub
) t, TABLE(xmlsequence(t.XML))
)
ORDER BY
CASE substr(answer,instr(answer,'=')+1)
     WHEN substr(answer,0,instr(answer,'*')-1)
     THEN 1
     WHEN substr(substr(answer,0,instr(answer,'*')-1),2) ||
        substr(substr(answer,0,instr(answer,'*')-1),0,2-1)
     THEN 2
     WHEN substr(substr(answer,0,instr(answer,'*')-1),3) ||
        substr(substr(answer,0,instr(answer,'*')-1),0,3-1)
     THEN 3
     WHEN substr(substr(answer,0,instr(answer,'*')-1),4) ||
        substr(substr(answer,0,instr(answer,'*')-1),0,4-1)
     THEN 4
     WHEN substr(substr(answer,0,instr(answer,'*')-1),5) ||
        substr(substr(answer,0,instr(answer,'*')-1),0,5-1)
     THEN 5
     WHEN substr(substr(answer,0,instr(answer,'*')-1),6) ||
        substr(substr(answer,0,instr(answer,'*')-1),0,6-1)
     THEN 6
END;
ANSWER
--------------------------------
142857*1 =142857
142857*3 =428571
142857*2 =285714
142857*6 =857142
142857*4 =571428
142857*5 =714285

6 rows selected.

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