OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed
« How to solve the Find Maximum Possible Product Puzzle in SQL

How to determine the table creation order in SQL »

How to solve the Largest 7-Digit Number Puzzle in SQL

January 18th, 2009 By Frank Zhou

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.

———————————————-10G 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.  

3 Responses to “How to solve the Largest 7-Digit Number Puzzle in SQL”

  1. newkid Says:
    June 24th, 2010 at 10:39 am 
    I think there's a bug in your code:
		  WHEN n in (4)
		  THEN CASE WHEN MOD(to_number(PRIOR N||N), n) = 0 THEN 1 END
WHEN LEVEL=2, this sould be: MOD(to_number(N||PRIOR N), n) = 0
WHEN LEVEL=3, this sould be: MOD(to_number(PRIOR N||CONNECT_BY_ROOT(n)), n) = 0

Mine in 11GR2:
WITH n AS (SELECT ROWNUM-1 n FROM DUAL CONNECT BY ROWNUM<=10)
,t(d1,d2,d3,d4,d5,d6,d7,num,lvl) AS (
SELECT n,0,0,0,0,0,0,TO_CHAR(n),1 FROM n
UNION ALL
SELECT d1
      ,DECODE(lvl,1,n,d2)
      ,DECODE(lvl,2,n,d3)
      ,DECODE(lvl,3,n,d4)
      ,DECODE(lvl,4,n,d5)
      ,DECODE(lvl,5,n,d6)
      ,DECODE(lvl,6,n,d7)
      ,CAST(n||num AS VARCHAR2(7))
      ,lvl+1
  FROM t,n
 WHERE INSTR(TO_CHAR(num),TO_CHAR(n))=0
       AND lvl2 AND MOD(d3*1000+d2*10+d1,8)=0
                                              )
            )
)
SELECT * FROM (
SELECT num FROM t
 WHERE lvl=7
       AND MOD(num,d1)=0 AND MOD(num,d2)=0 AND MOD(num,d3)=0 AND MOD(num,d4)=0 AND MOD(num,d5)=0
       AND MOD(num,d6)=0 AND MOD(num,d7)=0
ORDER BY num DESC
)
WHERE ROWNUM=1;

NUM
----------------------------------------
9781632
  2. newkid Says:
    June 24th, 2010 at 10:42 am

    Something is wrong with this site. The correct SQL should be:

    WITH n AS (SELECT ROWNUM-1 n FROM DUAL CONNECT BY ROWNUM<=10)
    ,t(d1,d2,d3,d4,d5,d6,d7,num,lvl) AS (
    SELECT n,0,0,0,0,0,0,TO_CHAR(n),1 FROM n
    UNION ALL
    SELECT d1
    ,DECODE(lvl,1,n,d2)
    ,DECODE(lvl,2,n,d3)
    ,DECODE(lvl,3,n,d4)
    ,DECODE(lvl,4,n,d5)
    ,DECODE(lvl,5,n,d6)
    ,DECODE(lvl,6,n,d7)
    ,CAST(n||num AS VARCHAR2(7))
    ,lvl+1
    FROM t,n
    WHERE INSTR(TO_CHAR(num),TO_CHAR(n))=0
    AND lvl2 AND MOD(d3*1000+d2*10+d1,8)=0
    )
    )
    )
    SELECT * FROM (
    SELECT num FROM t
    WHERE lvl=7
    AND MOD(num,d1)=0 AND MOD(num,d2)=0 AND MOD(num,d3)=0 AND MOD(num,d4)=0 AND MOD(num,d5)=0
    AND MOD(num,d6)=0 AND MOD(num,d7)=0
    ORDER BY num DESC
    )
    WHERE ROWNUM=1;

  3. newkid Says:
    June 24th, 2010 at 10:47 am

    my post got messed up again. try:

    WITH n AS (SELECT ROWNUM-1 n FROM DUAL CONNECT BY ROWNUM<=10)
,t(d1,d2,d3,d4,d5,d6,d7,num,lvl) AS (
SELECT n,0,0,0,0,0,0,TO_CHAR(n),1 FROM n
UNION ALL
SELECT d1
      ,DECODE(lvl,1,n,d2)
      ,DECODE(lvl,2,n,d3)
      ,DECODE(lvl,3,n,d4)
      ,DECODE(lvl,4,n,d5)
      ,DECODE(lvl,5,n,d6)
      ,DECODE(lvl,6,n,d7)
      ,CAST(n||num AS VARCHAR2(7))
      ,lvl+1
  FROM t,n
 WHERE INSTR(TO_CHAR(num),TO_CHAR(n))=0
       AND lvl<8
       AND (n=5 AND d1 IN (0,5)
            OR n IN (1,3,7,9)
            OR n IN (2,6) AND d1 IN (2,4,6,8,0)
            OR n=4 AND d1 IN (2,4,6,8,0) AND (lvl=1
                                              OR MOD(d2*10+d1,4)=0
                                              )
            OR n=8 AND d1 IN (2,4,6,8,0) AND (lvl=1
                                              OR lvl=2 AND MOD(n*1000+d2*10+d1,8)=0
                                              OR lvl NOT IN (1,2) AND MOD(d3*1000+d2*10+d1,8)=0
                                              )
            )
)
SELECT * FROM (
SELECT num FROM t
 WHERE lvl=7
       AND MOD(num,d1)=0 AND MOD(num,d2)=0 AND MOD(num,d3)=0 AND MOD(num,d4)=0 AND MOD(num,d5)=0
       AND MOD(num,d6)=0 AND MOD(num,d7)=0
ORDER BY num DESC
)
WHERE ROWNUM=1;

NUM
----------------------------------------
9781632

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question
« How to solve the Find Maximum Possible Product Puzzle in SQL

How to determine the table creation order in SQL »