## Puzzle of the Week #10: Fibonacci

With a single SELECT statement calculate first 20 Fibonacci numbers without using Binet’s formula.

Expected Result:

```   N     Fib(n)
---- ----------
1          1
2          1
3          2
4          3
5          5
6          8
7         13
8         21
9         34
10         55
11         89
12        144
13        233
14        377
15        610
16        987
17       1597
18       2584
19       4181
20       6765
```

## Solutions:

#1. Oracle 10g solution (using MODEL clause):

```SELECT n, f AS "Fib(n)"
FROM dual
MODEL
DIMENSION BY (0 d)
MEASURES (0 n, 0 f)
RULES ITERATE(20) (
f[iteration_number]=DECODE(iteration_number, 0,1, 1,1,
f[iteration_number-2]+f[iteration_number-1]),
n[iteration_number]=iteration_number+1
);

N     Fib(n)
--- ----------
1          1
2          1
3          2
4          3
5          5
6          8
7         13
8         21
9         34
10         55
11         89
12        144
13        233
14        377
15        610
16        987
17       1597
18       2584
19       4181
20       6765```

#2. Oracle 11.2g solution (using Recursive WITH clause):

WITH x(n, f1, f2) AS (
SELECT 1, 1, 1
FROM dual
UNION ALL
SELECT n+1, f2, f1+f2
FROM x
WHERE n<20
)
SELECT n, f1 AS “Fib(n)”
FROM x

#3. Oracle 12c solution (using WITH for PL/SQL function):

```WITH
FUNCTION fib(n INTEGER) RETURN NUMBER DETERMINISTIC
AS
BEGIN
RETURN CASE WHEN n IN (1,2) THEN 1
ELSE fib(n-2)+fib(n-1)
END;
END;
SELECT LEVEL n, fib(LEVEL) AS "Fib(n)"
FROM dual
CONNECT BY LEVEL<=20```