SQL & PL/SQL

Hi,

Something like this

select sum(col1) over (order by col2) from tab1

Regards
Anurag

If you are on 10g check out: [Oracle 10g Fibonacci Sequence|http://www.oracle.com/technology/oramag/code/tips2006/052906.html]

Not an optimal use of MODEL:

select  s seq
  from  dual
  model return all rows
  dimension by(0 d)
  measures(0 s)
  rules(
        s[1] = 1,
        s[for d from 2 to 12 increment 1] = s[cv()-2] + s[cv()-1]
       )
/

       SEQ
----------
         0
         1
         1
         2
         3
         5
         8
        13
        21
        34
        55

       SEQ
----------
        89
       144

13 rows selected.

SQL> 

SY.

with t as
(
select rownum-1 n, ((1+ sqrt(5))/2) phi from dual connect by rownum < 10
)
select n, round(( power(phi,n) - power(1 - phi,n))/sqrt(5)) as fib
from t

Gives:

N	FIB
0	0
1	1
2	1
3	2
4	3
5	5
6	8
7	13
8	21

;)

I just posted it as an example. :)

Or (of course) the xquery way (in 11g):

SQL> select * from xmltable('declare function local:f($i) {
                                       if ($i = 0 or $i = 1) 
                                         then 
                                           1 
                                         else 
                                           local:f($i - 1) + local:f($i - 2)                                       
                                  };
                                  element e {for $i in 0 to xs:int(n) return local:f($i)}' passing xmlelement("n",8)
                                  columns fibonacci varchar2(30) path '.')
/
FIBONACCI                     
------------------------------
1 1 2 3 5 8 13 21 34          
1 row selected.

In addition to the other examples, just for fun and since this is (again) a nice question:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15844208486026#1260264200346087371
http://en.wikipedia.org/wiki/Fibonacci_number

In this case, it is effective to use recursive with clause B-)
Hehe I have used PostgreSQL8.4 because I do not have Oracle11gR2 :8}

with recursive rec(SumVal,PreSum) as(
select 1,0
union all
select PreSum+SumVal,SumVal
from rec
where SumVal<21)
select*from rec;

 sumval | PreSum
--------+--------
      1 |      0
      1 |      1
      2 |      1
      3 |      2
      5 |      3
      8 |      5
     13 |      8
     21 |     13

Hehe I have used Math B-)
http://en.wikipedia.org/wiki/Fibonacci_number

select 1/sqrt(5)
*(power((1+sqrt(5))/2,rowNum)
 -power((1-sqrt(5))/2,rowNum)) as val
  from dict
 where RowNum <= 10;

VAL
---
  1
  1
  2
  3
  5
  8
 13
 21
 34
 55

In Oracle it would be sth like

SQL> with rec (n, f) as
(
  select 1 n, 1 f from dual union all
  select n + f, n from rec where n <= 21
)
--
--
select f from rec

         F
----------
         1
         1
         2
         3
         5
         8
        13
        21

8 rows selected.