SQL Puzzle | Generate Fibonacci Series in SQL Server

The Fibonacci Series puzzle. The Fibonacci Sequence is the series of numbers:

0, 1, 1, 2, 3, 5, 8, 13, 21, 34, …

The next number is found by adding up the two numbers before it.

The 2 is found by adding the two numbers before it (1+1)
Similarly, the 3 is found by adding the two numbers before it (1+2),
And the 5 is (2+3),
and so on!

For details please visit below urls-

https://en.wikipedia.org/wiki/Fibonacci_number
https://www.mathsisfun.com/numbers/fibonacci-sequence.html

Please check out the sample input values and sample expected output below.

Sample Input

Let’s say we need 20 Fibonacci numbers starting with 0

Expected Output

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to

pawankkmr@gmail.com

Solution 1 , Numbers Table

--

SELECT 0 Fibonacci
	UNION ALL
SELECT 
	ROUND((POWER(((1 + Sqrt(5))/2), Number) - POWER(-1/((1 + Sqrt(5))/2), Number )) / Sqrt(5) ,1) 
FROM Numbers
WHERE Number < 20 

--

SOLUTION – 2 | Using While Loop

--

DECLARE @Fibonacci TABLE (Number INT NOT NULL)
INSERT @Fibonacci (Number) SELECT 0 UNION ALL SELECT 1

WHILE (SELECT COUNT(*) FROM @Fibonacci) < 20
BEGIN
	INSERT INTO @Fibonacci (Number)
	SELECT SUM(Number) 
	FROM (SELECT TOP 2 Number FROM @Fibonacci ORDER BY Number DESC)t
END

SELECT Number Fibonacci FROM @Fibonacci
 

--

–Solution 3 , Recursive CTE

--

 
;WITH CTE AS
(
	SELECT  
		   0  AS Level,
           0  AS Start,
           1  AS Next
    UNION ALL
      SELECT  a.Level + 1            
           ,a.Next                    
           ,a.Start + a.Next
    FROM CTE a   
	WHERE a.Level < ( 20 - 1 )
)
SELECT Start Fibonacci FROM CTE
OPTION ( MAXRECURSION 10000 ) 

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com