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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
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,811 views