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