Forum Stats

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

Discussions

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

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
  • 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]
  • 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
  • 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
    ;)
  • 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. :)
  • 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
  • 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
  • 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
  • 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
  • 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.
This discussion has been closed.