Forum Stats

  • 3,824,981 Users
  • 2,260,447 Discussions
  • 7,896,369 Comments

Discussions

Categories

add previous numer with current numebr

152933
152933 Member Posts: 404
edited Dec 19, 2009 1:13AM in SQL & PL/SQL
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..

to be generated upto 21 .Please help on this

0 1 1 2 3 5 8 13 21

Regards,
MR
· Share on TwitterShare on Facebook

Answers

  • Anurag Tibrewal
    Anurag Tibrewal Member Posts: 3,901 Gold Trophy
    Hi,

    Something like this
    select sum(col1) over (order by col2) from tab1
    Regards
    Anurag
    · Share on TwitterShare on Facebook
  • Centinul
    Centinul 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]
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,456 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.
    User_ZUUOD
    · Share on TwitterShare on Facebook
  • AlanWms
    AlanWms 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
    ;)
    · Share on TwitterShare on Facebook
  • Centinul
    Centinul 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. :)
    · Share on TwitterShare on Facebook
  • MichaelS
    MichaelS 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.
    User_ZUUOD
    · Share on TwitterShare on Facebook
  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    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
    · Share on TwitterShare on Facebook
  • Aketi Jyuuzou
    Aketi Jyuuzou 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
    · Share on TwitterShare on Facebook
  • Aketi Jyuuzou
    Aketi Jyuuzou 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
    · Share on TwitterShare on Facebook
  • MichaelS
    MichaelS 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.
    · Share on TwitterShare on Facebook
This discussion has been closed.