Fibonacci numbers in SQL

Example for versions Oracle 10g SQL, Oracle 11g SQL

Pure SQL doesn’t support loops, recursions or user-defined functions. Besides, concatenating fields from multiple rows of a table or a subquery is not a standard aggregate function. This example uses:

  • Binet’s formula and math functions round, power and sqrt to calculate n-th Fibonacci number;
  • pseudocolumn level to construct a pseudotable t1 containing numbers 1 through 16;
  • built-in function SYS_CONNECT_BY_PATH to concatenate the resulting numbers in ascending order. 
 SELECT REPLACE(MAX(SYS_CONNECT_BY_PATH(fib||', ', '/')),'/','')||'...' fiblist 
   FROM ( 
    SELECT n, fib, ROW_NUMBER() 
      OVER (ORDER BY n) r 
      FROM (select n, round((power((1+sqrt(5))*0.5, n)-power((1-sqrt(5))*0.5, n))/sqrt(5)) fib 
              from (select level n
                      from dual
                   connect by level <= 16) t1) t2
 ) 
  START WITH r=1 
CONNECT BY PRIOR r = r-1;