OraQA

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

Find the least number using 0-9 exactly once that is evenly divisible by each of these digits.

This SQL pattern is based on the folloiwng logic:

1) To get a multiple of both 2 and 5, the last digit must be 0,

2) To get a multiple of 8 (and 4), the tens digit must be even,

3) The hundreds digit must be odd if the tens digit is 2 or 6, and even otherwise.

4) The sum of the digits is 45, any permutation of the digits gives a multiple of 3 and 9

5) The number will also be divisible by 6, since it is divisible by 2 and 3,

By applying the above logic , we can skip the divisibility checking on 0, 1, 2, 3, 4, 5, 6, 8, 9

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

SELECT min(divisible_num) min_divisible_num
FROM
(  WITH NUM AS (SELECT LEVEL - 1 n FROM dual CONNECT BY LEVEL <= 10)
 SELECT divisible_num
 FROM
 (SELECT to_number(utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(
replace(sys_connect_by_path(n,','),','))))) divisible_num
   FROM (SELECT n FROM NUM)
  WHERE LEVEL = 10
  CONNECT BY NOCYCLE n != PRIOR n
  AND CONNECT_BY_ROOT(n) = 0
  AND CASE LEVEL
           WHEN 2
           THEN CASE WHEN MOD(n, 2) = 0 THEN 1 END
           WHEN 3
           THEN CASE WHEN PRIOR n IN (2, 6)
                     THEN CASE WHEN MOD(n, 2) = 1 THEN 1 END
                     ELSE CASE WHEN MOD(n, 2) = 0 THEN 1 END
                END
           ELSE 1 END = 1
  AND LEVEL <=10
  ), (SELECT n, count(*) over() cnt
      FROM ( SELECT n
              FROM
             (SELECT n, SUM(n) OVER () SUM_NUM FROM NUM)
              WHERE CASE WHEN n IN (3, 9)
                         THEN CASE WHEN MOD(SUM_NUM, n) = 0 THEN 0 END
                         WHEN n IN (0,1,2,4,5,8) THEN 0
                         WHEN n = 6
                         THEN CASE WHEN MOD(SUM_NUM, 3) = 0 THEN 0 END
                         ELSE 1
                    END = 1
          )
  )
 GROUP BY divisible_num, cnt
 HAVING COUNT(CASE WHEN MOD(divisible_num, n) = 0 THEN 1 END) = cnt
);

MIN_DIVISIBLE_NUM
----------------------
  1234759680
 

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