Discussion on: Challenge: Write the recursive Fibonacci algorithm in a different language.

[Rob Hoelz]

I haven't seen a SQL implementation yet, so here's one:

with recursive fib(a, b) as
    (select 1 as a, 0 as b

     union

     select a + b as a, a as b from fib)

select a from fib limit 10;

[Avalander]

👏👏 I never thought you could do that with SQL!

[Ross Henderson]

Here it is in Oracle SQL:

with FIBONACCI (i, SPIRAL, PREV) as 
(
   select 
      1 i, 0 SPIRAL, cast(null as number) PREV 
   from 
      DUAL
   union all
   select 
      f1.i + 1 i, f1.SPIRAL + nvl(f1.PREV,1) SPIRAL, f1.SPIRAL PREV
    from 
       FIBONACCI f1
    where 
   f1.i < 605
)

select SPIRAL from FIBONACCI order by i;

The limit of 605 is the limit before numeric overflow.

[Slavius]

Here's interesting one for SQL Server with maximum value before arithmetic overflow due to data type limit:

SET STATISTICS TIME,PROFILE ON

DECLARE @MAX SMALLINT
SET @MAX = 1474

SELECT TOP 1 s.Fib FROM (
SELECT
    FLOOR(POWER(( 1 + SQRT(5) ) / 2.0, number) / SQRT(5) + 0.5) AS Fib
FROM master..spt_values
WHERE TYPE = 'p' AND number BETWEEN 1 AND @MAX
) AS s
ORDER BY s.Fib DESC