factorial of a number

Mac_Freak_Rahul · Apr 18, 2011 11:42AM

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

Ganesh Srivatsav · Apr 18, 2011 11:50AM

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.

Frank Kulash · Apr 18, 2011 11:49AM

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.

Solomon Yakobson · Apr 18, 2011 11:50AM

select  round(exp(sum(ln(level)))) factorial
  from  dual
  connect by level <= 10
/

 FACTORIAL
----------
   3628800

SQL>

SY.

Ganesh Srivatsav · Apr 18, 2011 11:50AM

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.

Mac_Freak_Rahul · Apr 18, 2011 12:07PM

Thanks Ganesh , SY ,Frank for providing me the answer

Regards
Rahul

pollywog · Apr 18, 2011 12:21PM

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 · Apr 18, 2011 12:27PM

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 · Apr 18, 2011 12:31PM

hey I gave a shout out to the model

Solomon Yakobson · Apr 18, 2011 12:45PM

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 · Apr 19, 2011 6:13AM

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.

Forum Stats

Quick Links

Categories

factorial of a number

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

factorial of a number

Best Answer

Answers