alter session set nls_date_format = 'dd/mm/yyyy';
create table a_ (
client char(1),
dt date,
item_id number,
pos_basis number,
qty number
);
insert into a_ values('A','20/3/2003',1001,3,1000);
insert into a_ values('A','21/2/2003',1001,3, 10);
insert into a_ values('A','21/3/2003',1001,4,1000);
insert into a_ values('A','23/2/2003',1001,4, 10);
insert into a_ values('B','20/3/2003',1001,3,1000);
insert into a_ values('B','21/2/2003',1002,3, 10);
insert into a_ values('B','21/3/2003',1003,3,1000);
insert into a_ values('B','23/2/2003',1004,3, 10);
select
client, dt, item_id, pos_basis, qty
from (
select
client, dt, item_id, pos_basis, qty ,
rank() over (partition by dt,item_id,pos_basis
order by dt desc) r
from
a_
where
dt < '1/3/2003'
)
where r = 1;
drop table a_;