René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Retrieve most actual row with SQL

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_;