Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Compare 2 consecutives rows in atable 1

Status
Not open for further replies.

fosa

IS-IT--Management
Mar 18, 2008
26
FR
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



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

Code:
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
 
Thanks it is very usefull,and simple ;-)

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top