Forum Stats

  • 3,827,745 Users
  • 2,260,818 Discussions
  • 7,897,364 Comments

Discussions

factorial of a number

Mac_Freak_Rahul
Mac_Freak_Rahul Member Posts: 427
edited Apr 19, 2011 6:13AM in SQL & PL/SQL
Hi all,

I am trying to write a sql that can calculate the factorial from a single sql.with this query I can reach uptill :

with t as
(select 10 num from dual)
select ltrim(max(sys_connect_by_path(rownum,'*')),'*') factorial
from t
connect by rownum <= num

Output: 1*2*3*4*5*6*7*8*9*10

Now my requirements are as follows:

1) great if some1 could help me in writing an outer query on top of mine to get the desired result
2) suggest some other way to make me understand how to calculate factorial of a number from scratch.

Thanks
Rahul

Best Answer

  • Ganesh Srivatsav
    Ganesh Srivatsav Member Posts: 2,284 Silver Trophy
    Try this,
    SQL> WITH T
      2       AS (SELECT LEVEL num
      3             FROM DUAL
      4           CONNECT BY LEVEL < = 10)
      5  SELECT num, EXP (SUM (LN (lvl))) factorial
      6    FROM (SELECT LEVEL lvl
      7            FROM (SELECT MAX (num) mx FROM T)
      8          CONNECT BY LEVEL <= mx),
      9         T
     10   WHERE lvl<=num
     11   GROUP BY num;
    
           NUM  FACTORIAL
    ---------- ----------
             1          1
             2          2
             3          6
             4         24
             5        120
             6        720
             7       5040
             8      40320
             9     362880
            10    3628800
    
    10 rows selected.
    
    SQL> 
    G.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,072 Red Diamond
    edited Apr 18, 2011 12:33PM
    Hi, Rahul,

    Starting in Oracle 11.2, you can use a recursive WITH clause (instead of CONNCT BY) to do that easily.

    For earlier versions, see this thread:
    4092659

    n! is a very special case; it can be done without CONNECT BY, XML functions or PL/SQL, like this:
    SELECT	ROUND (EXP (SUM (LN (LEVEL))))	AS f
    FROM	dual
    CONNECT BY	LEVEL <= 10	-- or any positive integer
    ;
    Edited by: Frank Kulash on Apr 18, 2011 11:53 AM

    Edited by: Frank Kulash on Apr 18, 2011 11:54 AM
    Solomon posted the same ROUND(EXP (SUM( LN ... solution independently, whicle I was posting mine.
    Frank Kulash
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,488 Red Diamond
    select  round(exp(sum(ln(level)))) factorial
      from  dual
      connect by level <= 10
    /
    
     FACTORIAL
    ----------
       3628800
    
    SQL> 
    SY.
    Solomon Yakobson
  • Ganesh Srivatsav
    Ganesh Srivatsav Member Posts: 2,284 Silver Trophy
    Try this,
    SQL> WITH T
      2       AS (SELECT LEVEL num
      3             FROM DUAL
      4           CONNECT BY LEVEL < = 10)
      5  SELECT num, EXP (SUM (LN (lvl))) factorial
      6    FROM (SELECT LEVEL lvl
      7            FROM (SELECT MAX (num) mx FROM T)
      8          CONNECT BY LEVEL <= mx),
      9         T
     10   WHERE lvl<=num
     11   GROUP BY num;
    
           NUM  FACTORIAL
    ---------- ----------
             1          1
             2          2
             3          6
             4         24
             5        120
             6        720
             7       5040
             8      40320
             9     362880
            10    3628800
    
    10 rows selected.
    
    SQL> 
    G.
  • Thanks Ganesh , SY ,Frank for providing me the answer

    Regards
    Rahul
  • pollywog
    pollywog Member Posts: 1,006 Gold Badge
    here is another way
    /* Formatted on 10/22/2010 8:28:21 AM (QP5 v5.149.1003.31008) */
    SELECT id num, running_total
      FROM DUAL
    MODEL RETURN UPDATED ROWS
       DIMENSION BY (0 d)
       MEASURES (CAST (NULL AS NUMBER) id, 1 running_total)
       RULES
          ITERATE (10)
          (id [ITERATION_NUMBER] = ITERATION_NUMBER + 1,
           running_total[ITERATION_NUMBER] =  case  when iteration_number > 1 then id[cv()] * running_total[cv() - 1]else iteration_number+1 end )
    NUM	RUNNING_TOTAL
    1	1
    2	2
    3	6
    4	24
    5	120
    6	720
    7	5040
    8	40320
    9	362880
    10	3628800
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,072 Red Diamond
    edited Apr 18, 2011 12:44PM
    Hi, Rahul,

    Here's an Oracle 11.2 solution, using a recursive WITH clause:
    WITH	f (n, n_factorial)	AS
    (
    	SELECT	1	AS n
    	,	1	AS n_factorial
    	FROM	dual
    		--
        UNION ALL
    		--
    	SELECT  n + 1			AS n
    	,	n_factorial * (n + 1)	AS n_factorial
    	FROM	f
    	WHERE	n	< 10
    )
    SELECT	*
    FROM	f
    ;
    Output:
    `        N N_FACTORIAL
    ---------- -----------
             1           1
             2           2
             3           6
             4          24
             5         120
             6         720
             7        5040
             8       40320
             9      362880
            10     3628800
    To summarize what (I believe) everyone is saying:

    Multiplying a list of numbers (including consecutive integers, generated by CONNECT BY) can be done by the SUM function, together with EXP and LN.
    This has been known since the time of the deluge. When Noah brought all the animals out of the ark, and told them to "be fruitful and multiply" (see Genesis 8.17), some of the snakes objected, saying that they were only adders, and couldn't multiply. Noah showed them how to cut tree limbs into pieces, stick four of the peices upright in the ground, and build a horizontal platform on top with the other pieces; then they were all set, because even adders can multiply if they have a log table.

    However, multiplying is a special case. When you have a recursive relationship, where the value on one level is based on the value at a previous level, then you can use a recursive WITH clause (starting in Oracle 11.2) or XMLQUERY (in Oracle 10, and up), or write a user-defined function that will take a string such as '1*2*3*4' and return the result of that computation.
    MODEL is another option that nobody has mentioned yet. It's probably more work in most cases. (Pollywog did mention it, while I was posting this.)

    If the data is not really coming from a table (as is the case here), a user-defined funciton in PL/SQL might be appropriate.
    Starting in in Oracle 9, you can write user-defined aggregate/analytic functions.

    Edited by: Frank Kulash on Apr 18, 2011 12:32 PM
  • pollywog
    pollywog Member Posts: 1,006 Gold Badge
    hey I gave a shout out to the model :)
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,488 Red Diamond
    pollywog wrote:
    hey I gave a shout out to the model :)
    No need to iterate and no need for CASE:
    SELECT  num,
            factorial
      FROM  DUAL
        MODEL
          DIMENSION BY (1 num)
          MEASURES (1 factorial)
          RULES (
                 factorial[for num from 2 to 10 increment 1] =  cv(num) * factorial[cv() - 1]
                )
    /
    
           NUM  FACTORIAL
    ---------- ----------
             1          1
             2          2
             3          6
             4         24
             5        120
             6        720
             7       5040
             8      40320
             9     362880
            10    3628800
    
    10 rows selected.
    
    SQL> 
    SY.
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    One more for the record:
    SQL> select rownum - 1 n, to_number(column_value) factorial
      from xmltable('declare function local:fac ($i) {
                              if ($i = 0) then
                               1
                              else
                               $i * local:fac($i - 1)
                            }; (::)
                            for $i in 0 to 10 return local:fac($i)')
    /
             N  FACTORIAL
    ---------- ----------
             0          1
             1          1
             2          2
             3          6
             4         24
             5        120
             6        720
             7       5040
             8      40320
             9     362880
            10    3628800
    
    11 rows selected.
This discussion has been closed.