OraQA

The following is an interesting problem posted by mathforum.org:

Work out the largest 7-digit number you can applying 2 rules only:

1) every digit in the number must be able to be divided into the number;
2) no digit can be repeated.

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

SELECT  str_num
FROM
(SELECT str_num
  FROM
  (SELECT str_num, to_number(substr(str_num,LEVEL,1)) n1
   FROM
   (SELECT to_number(utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(
           replace(sys_connect_by_path(n,','),',')))))  str_num
    FROM (SELECT LEVEL n FROM dual CONNECT BY LEVEL <10)
    WHERE LEVEL = 7
    CONNECT BY NOCYCLE PRIOR n != n
    AND LEVEL < 8
    AND CASE LEVEL
        WHEN 2
	THEN CASE WHEN n in (2, 6, 8)
	          THEN CASE WHEN MOD(CONNECT_BY_ROOT(n), 2) = 0 THEN 1 END
		  WHEN n in (4)
		  THEN CASE WHEN MOD(to_number(PRIOR N||N), n) = 0 THEN 1 END
		  WHEN n = 5
		  THEN CASE WHEN PRIOR N in (5) THEN 1 END
		  ELSE 1 END
	WHEN 3
	THEN CASE WHEN n in (2, 6)
		  THEN CASE WHEN MOD(CONNECT_BY_ROOT(n), 2) = 0 THEN 1 END
		  WHEN n in (4)
		  THEN CASE WHEN MOD(to_number(PRIOR N||N), n) = 0 THEN 1 END
		  WHEN n in (8)
		  THEN CASE WHEN MOD(to_number(CONNECT_BY_ROOT(n)||PRIOR N||N),n)= 0
			    THEN 1 END
	          WHEN n = 5
		  THEN CASE WHEN CONNECT_BY_ROOT(n) in (5) THEN 1 END
		  ELSE 1 END
        WHEN 4
        THEN CASE WHEN n in (2, 4, 6 ,8)
		  THEN CASE WHEN MOD(CONNECT_BY_ROOT(n), 2) = 0 THEN 1 END
		  WHEN n = 5
                  THEN CASE WHEN CONNECT_BY_ROOT(n) in (5) THEN 1 END
		  ELSE 1 END
	WHEN 5
        THEN CASE WHEN n in (2, 4, 6 ,8)
		  THEN CASE WHEN MOD(CONNECT_BY_ROOT(n), 2) = 0 THEN 1 END
		  WHEN n = 5
	          THEN CASE WHEN CONNECT_BY_ROOT(n) in (5 ) THEN 1 END
		  ELSE 1 END
	WHEN 6
        THEN CASE WHEN n in (2, 4, 6 ,8)
		  THEN CASE WHEN MOD(CONNECT_BY_ROOT(n), 2) = 0 THEN 1 END
		  WHEN n = 5
                  THEN CASE WHEN CONNECT_BY_ROOT(n) in (5) THEN 1 END
		  ELSE 1 END
        WHEN 7
        THEN CASE WHEN n in (2, 4, 6 ,8)
		  THEN CASE WHEN MOD(CONNECT_BY_ROOT(n), 2) = 0 THEN 1 END
		  WHEN n = 5
		  THEN CASE WHEN CONNECT_BY_ROOT(n) in (5) THEN 1 END
		  ELSE 1 END
        ELSE 1 END = 1
      )
      CONNECT BY PRIOR str_num = str_num
      AND LEVEL <= length(str_num)
      AND PRIOR DBMS_RANDOM.STRING ('P',20) IS NOT NULL
   )
   GROUP BY str_num
   HAVING count(CASE WHEN mod(str_num, n1) = 0
                     THEN 1 END ) = length(str_num)
   ORDER BY str_num DESC
)
WHERE ROWNUM  = 1;

STR_NUM
———-
9867312

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