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-

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

Fibonacci
0
1
1
2
3
5
8
13
21
34
55
89
144
233
377
610
987
1597
2584
4181

Rules/Restrictions

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

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