Fibonacci sequences - SQL Server Forums

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)

Fibonacci sequences

Author

Topic

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-06 : 12:49:32

Hi all.
I want to create a query that calculates Fibonacci sequences then inserts them to the table.
But the problem is: usnig only once at query and not usnig loop.

Here is the table


CREATE TABLE Fib
(
	SentID INT PRIMARY KEY,
	Number BIGINT NOT NULL
)

INSERT Fib
SELECT 1,'1' UNION ALL
SELECT 2,'1'

Here is my solution:


DECLARE @a INT
SET @a=0

WHILE @a<10
BEGIN
	INSERT Fib
	SELECT SentID=MAX(SentID)+1, SUM(Number) 
	FROM (
			SELECT TOP 2 SentID , Number
			FROM Fib
			ORDER BY Number DESC
		) D
SET @a=@a+1;
END

After execution loop:


SentID      Number
----------- ---------
1           1
2           1
3           2
4           3
5           5
6           8
7           13
8           21
9           34
10          55
11          89
12          144

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 13:24:35

Well, your solution for the homework is using loop.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 13:26:22

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130689

N 56°04'39.26"
E 12°55'05.63"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-06 : 13:41:13

philosophical question:

does a recursive CTE satisfy a loopless requirment?

Be One with the Optimizer
TG

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-06 : 14:42:50

Okay. I got the answer at the link.
The answer was recursive common table expression.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-26 : 06:31:05

[code];WITH Fibonacci(n, f, f1)
AS (
SELECT CAST(1 AS BIGINT),
CAST(0 AS BIGINT),
CAST(1 AS BIGINT)

UNION ALL

SELECT n + 1,
f + f1,
f
FROM Fibonacci
WHERE n < 93
)

SELECT n,
f AS Fibonacci
FROM Fibonacci[/code]

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-26 : 06:54:51

As usual, I go with Quirky Update



declare @fib table(fib_id int,current_val bigint)

declare @current_val bigint, @previous_val bigint,@new_val bigint

--Set how many rows you want to generate the series
insert into @fib(fib_id,current_val ) 
select top 93 row_number() over(order by name),1 from sysobjects 

select @current_val =0, @previous_val=-1,@new_val =1 
 

update 
	@fib 
set 
	current_val =@current_val,
	@current_val =@new_val +@previous_val,
	@previous_val=@new_val, 
	@new_val=@current_val 

select * from @fib

More on Quirky Update
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-26 : 07:12:06

quote:

Originally posted by ms65g

Here is my solution:

DECLARE @a INT
SET @a=0

WHILE @a<10
BEGIN
	INSERT Fib
	SELECT SentID=MAX(SentID)+1, SUM(Number) 
	FROM (
			SELECT TOP 2 SentID , Number
			FROM Fib
			ORDER BY Number DESC
		) D
SET @a=@a+1;
END

You can always use IDENTITY column

DECLARE	@Fib TABLE
	(
		n TINYINT IDENTITY(1, 1),
		f BIGINT
	)


INSERT @Fib SELECT 0
INSERT @Fib SELECT 1

WHILE SCOPE_IDENTITY() < 93
	INSERT	@Fib
		(
			f
		)
	SELECT	SUM(f)
	FROM	(
			SELECT TOP 2	f
			FROM		@Fib
			ORDER BY	f DESC
		) AS x

SELECT	*
FROM	@Fib

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources