Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

analytical functions?(maybe)

alinuxJun 11 2008 — edited Jul 12 2008
Hi all,

I have a table like this
contract         amount        seq_id
------------------------------------------------------
1                      6                1            
1                      -70             2
2                      12             3
1                      -40             4
2                      -1               5
5                      8                6
6                      3                7
1                      6                8

create table a (contract number, amount number,seq_id number);
insert into a values (1,6,1);
insert into a values (1,-70,2);
insert into a values (2,12,3);
insert into a values (1,-40,4);
insert into a values (2,-1,5);
insert into a values (5,8,6);
insert into a values (6,3,7);
insert into a values (1,6,8);
what is the best solution to select all the contracts with their seq_id that have for the last seq_id (max (seq_id)) a negative amount.

in my example I will only select the contract 2 with seq_id = 5 because on his last seq_id it have a negative amount.

Can this be done doing only one query on my table, without using a correlated query?

Thanks

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 9 2008
Added on Jun 11 2008
12 comments
2,930 views