finding n! (n factorial) using recursive CTE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

finding n! (n factorial) using recursive CTE

Author

Topic

dev2dev
Starting Member

48 Posts

Posted - 2007-12-28 : 04:32:50

Hi,

I was trying to write a query to find n factorial using recursive cte


declare @n int
set @n = 6;

      WITH CTE (N) AS(
    SELECT CASE WHEN @N<0 THEN NULL ELSE 1 END N
 UNION ALL
    SELECT N*(N+1)
      FROM CTE
     WHERE N < @N
)

SELECT MAX(N) FROM CTE

I don't know where I went wrong, its showing wany one help me correcting this

thanks in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-28 : 04:40:07

It is as simple as

declare @n int,@result float
select @n = 6,@result=1
select @result=@result*number from master..spt_values where type='p' and number between 1 and @n
select @result

Madhivanan

Failing to plan is Planning to fail

dev2dev
Starting Member

48 Posts

Posted - 2007-12-28 : 05:05:07

wow!

i didn't know about spt_values, its cool

but i am doing research on cte :), trying to uderstand recursive CTEs, so wanted to do in old example of finding factorial using recursion

any way i got it

declare @n int,@r int
set @r=1
set @n = 6;

WITH CTE (N) AS(
SELECT CASE WHEN @N<0 THEN NULL ELSE 1 END N
UNION ALL
SELECT (N+1)
FROM CTE
WHERE N < @n
)

SELECT @r = @r*n from cte
print @r

btw: can u put some light on spt_values?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-28 : 06:08:53

I used master..spt_values just to make use of numbers from 1 to @n

Run this and see what it returns

declare @n int
set @n=6
select number from master..spt_values where type='p' and number between 1 and @n

Your CTE does the same thing

Madhivanan

Failing to plan is Planning to fail

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2007-12-29 : 08:25:00

You need to keep both a counter and an accumulator for the factorial, like this (I didn't bother handling the @n < 1 case):


declare @n int
set @n = 6;

WITH CTE (N, F) AS (
    SELECT 1, 1

    UNION ALL

    SELECT N+1, F*(N+1)
    FROM CTE
)
SELECT TOP 1 F FROM CTE
WHERE N = @n

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-29 : 13:56:29

Just a thought... Recurrsion is costly performance wise... about the only place you might need it is for the resolution of Hierarchies and, even then, there are better ways and many involve using a "Tally" table (table with a column of numbers... most of us build one permanently) like Madhivanan did.

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources