how to increment values based on their previous result? select 100*level as seq from dual connect by level<=5; current o/p: SEQ ------ 100 200 300 400 500 expected o/p: SEQ ----- 100 (100*1)=100 200 (100*2)=200 600 (200*3)=600 2400 (600*4)=2400 12000 (2400*5)=12000

100*level gives you what you received; there is no error or bug as the output is correct. Your expectations aren't based on the behavior of the expression since the 100 is a hard-coded value and won't change. You could try this: Code (SQL): SQL> SELECT seq*rownum FROM 2 (SELECT 100*level AS seq FROM dual 3 CONNECT BY level<=5); SEQ*ROWNUM ---------- 100 400 900 1600 2500 SQL> but that doesn't give you what you want, either. You can, of course, manufacture your desired results: Code (SQL): SQL> SELECT seq FROM 2 (SELECT 100*level AS seq FROM dual 3 CONNECT BY level<=5) 4 WHERE seq <= 200 5 UNION 6 SELECT seq*(rownum+1) FROM 7 (SELECT 100*level AS seq FROM dual 8 CONNECT BY level<=5) 9 WHERE seq = 300 10 UNION 11 SELECT seq*(rownum+2) FROM 12 (SELECT 100*level AS seq FROM dual 13 CONNECT BY level<=5) 14 WHERE seq = 400 15 UNION 16 SELECT seq*(rownum+5) FROM 17 (SELECT 100*level AS seq FROM dual 18 CONNECT BY level<=5) 19 WHERE seq = 400 20 UNION 21 SELECT seq*(rownum+2)*10 FROM 22 (SELECT 100*level AS seq FROM dual 23 CONNECT BY level<=5) 24 WHERE seq = 400; SEQ ---------- 100 200 600 1200 2400 12000 6 ROWS selected. SQL> There isn't a simple way to do what you want since you're referencing calculated values from the previous row.

Yes David. As the result can be only manipulated based on the previous row., I achieved it using function. But he asked me to achieve it using the query. For your note, it is an interview question...

Hi all., I got the query which gives me the expected result, thru surfing.. Code (SQL): SELECT round(EXP(SUM(ln(t1.n))))*100 AS seq FROM ( SELECT level n FROM dual CONNECT BY level <= 5) t1, ( SELECT level n FROM dual CONNECT BY level <= 5) t2 WHERE t1.n<=t2.n GROUP BY t2.n ORDER BY t2.n; SEQ ------- 100 200 600 2400 12000 But I cant get Y they're using functions like exp,ln,sum..... Cud U help me in getting, how it works,.

Well u can use lag function here to get the desired output SELECT ser, seq, lag ( seq,1,seq) over ( order by ser ) prev, ( lag ( seq,1,seq) over ( order by ser ) ) * ser prev_value FROM ( select rownum ser, 100*level as seq from dual connect by level<=5 ) Hope it helped you