Compare 2 consecutives rows in atable 1

[fosa]

Hi all,
I am under Oacle 10G R1
I can use PL/sql sql

How can I compare 2 consecutive rows in a table

and show the result, I want to check the value > 0

this is the list

A1 110
A2 112
A3 112.3
A4 112.4
A5 112.5
A11 113
A12 113.2
A21 114
A23 106
A31 118
A33 120
A34 122
A35 123
A36 124
A37 124.1

I want to do
A1-A2
A2-A3
...
Ai-1 -Ai

and I cannot determine in advance that I will have the number of rows

Thanks

 

[Dagon]

Easiest way is to use lead/lag e.g.

create table adata (aval varchar2(10), aamount number);

insert into adata values ('A1', 110);
insert into adata values ('A2', 112);
insert into adata values ('A3', 112.3);
insert into adata values ('A4', 112.4);
insert into adata values ('A5', 112.5);
insert into adata values ('A11', 113);
insert into adata values ('A12', 113.2);
insert into adata values ('A21', 114);
insert into adata values ('A23',  106);
insert into adata values ('A31', 118);
insert into adata values ('A33', 120);
insert into adata values ('A34', 122);
insert into adata values ('A35', 123);
insert into adata values ('A36', 124);
insert into adata values ('A37', 124.1);

select aval, lead(aamount) over (order by to_number(substr(aval,2)))-aamount as nxt_amount
from adata

 

[fosa]

Thanks it is very usefull,and simple ;-)