## Forum Stats

• 3,824,971 Users
• 2,260,446 Discussions

Discussions

# add previous numer with current numebr

Member Posts: 404
edited Dec 19, 2009 1:13AM
Hi All,

i want to add previous numer with current numebr ,that total numer with current number and so on

ex:

first 0+1=1
1+1=2
2+1=3
3+2=5
5+3=8..

0 1 1 2 3 5 8 13 21

Regards,
MR

• Member Posts: 3,901 Gold Trophy
Hi,

Something like this
`select sum(col1) over (order by col2) from tab1`
Regards
Anurag
• Member Posts: 6,871 Bronze Crown
If you are on 10g check out: [Oracle 10g Fibonacci Sequence|http://www.oracle.com/technology/oramag/code/tips2006/052906.html]
• Member Posts: 19,455 Red Diamond
Centinul wrote:
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.
• Member Posts: 368
```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```
• Member Posts: 6,871 Bronze Crown
edited Dec 18, 2009 11:18AM
Solomon Yakobson wrote:
Centinul wrote:
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:
I just posted it as an example.
• Member Posts: 8,424 Bronze Crown
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.```
• Member Posts: 16,087 Gold Crown
In addition to the other examples, just for fun and since this is (again) a nice question:
http://en.wikipedia.org/wiki/Fibonacci_number
• Member Posts: 1,072 Bronze Badge
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```
• Member Posts: 1,072 Bronze Badge
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```
• Member Posts: 8,424 Bronze Crown
I have used PostgreSQL8.4 because I do not have Oracle11gR2 :8}
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.```
This discussion has been closed.