1. Junior Member
Join Date
Apr 2003
Location
Delhi
Posts
51

## Query problem

hi all,
I have a table in which Amount1 ,Amount2 are two attributes.
Is there any way in SQL where I can compare Amount2 of a row
with Amount1 of the next row.

For eg.
Amount1 Amount2
100 10
90 15
15 18

Now I want to Select Amount1 only if Amount2 of second row is equal to Amount1 of next row which holds for second and third rows in this case.

2. If I understood your question correctly you can look into LEAD and LAG functions.

Join Date
Mar 2001
Location
Posts
598
try this...

create table test(Amount1 number, Amount2 number);
insert into test values(100, 10);
insert into test values(90, 15);
insert into test values(15, 18);
commit;
select Amount1, Amount2 from test;

select Amount1, lag(Amount2) over (order by amount2) from test;

Cheers!

Join Date
Mar 2001
Location
Posts
598
try this...

I have also included the query.

create table test(Amount1 number, Amount2 number);
insert into test values(100, 10);
insert into test values(90, 15);
insert into test values(15, 18);
insert into test values(18, 20);
commit;
select Amount1, Amount2 from test;

select Amount1, lag(Amount2) over (order by amount2) from test;

select a.Amount1, x.lag_amt from test a, (
select Amount1, lag(Amount2) over (order by amount2) lag_amt from test) x
where a.Amount1 = x.lag_amt;

Cheers!

5. Junior Member
Join Date
Apr 2003
Location
Delhi
Posts
51
thanx balaji & Sanjay
I think that wud solve my prob...

Originally posted by balajiyes
try this...

create table test(Amount1 number, Amount2 number);
insert into test values(100, 10);
insert into test values(90, 15);
insert into test values(15, 18);
commit;
select Amount1, Amount2 from test;

select Amount1, lag(Amount2) over (order by amount2) from test;

Cheers!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•