PL/SQL :: Compare A Row Of A Table With Its Previous Row / Rows In A Query

create table a(sourcerow number(2),  test_level number(2),  dpn varchar2(1),  qty number(5)); T

he insert scripts are as follows:　 

insert into a  values(1,3,'Y',5); insert into a values(2,2,'Y',4);  insert into a values(3,3,'N',3); insert into a  values(4,4,'Y',3);  insert into a  values(5,1,'N',6);  insert into a values(6,2,'N',5);  insert into a  values(7,2,'Y',4);  insert into a  values(8,3,'N',2);  insert into a values(9,4,'Y',2);  insert into a  values(10,1,'Y',3); .  SQL>select * from v$version;  Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production 

My logic should have the output as follows without 5th column:  sourcerowleveldpnqtyremark13Y522Y4This row in main table shouldn't be displayed in the query result as it has

dpn='N'44Y3*3=9In this row qty=9 will bedisplayed.

It will compare the value of level column with its previous row level col,if it is the parent of the current(ex.current is 4 and previous is 3 parent of 4),then it will check the dpn of previous row ,if dpn='N' then qty of parent will be multiplied with qty of current row and displayed under qty column.this row will not be displayed as dpn='N'this row will not be displayed as dpn='N'72Y4*6=24in its previous row level value is same so it will check the previous to previous row  where level is 1(parent of current row) and dpn='N' ,then it will multiply the qty of that row with current row and display the value in qty column.this row will not be displayed as dpn='N'94Y2*2=4In this row qty=4 will be displayed.It will compare the value of level column with its previous row level col,if it is the parent of the current(ex.current is 4 and previous is 3 parent of 4),then it will check the dpn of previous row ,if dpn='N' then qty of parent will be multiplied with qty of current row and displayed under qty column.101Y3It will not check for the previous rows as level 1 doesn't have any parent.