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

Create View in Oracle

This view shows the most actual prize in table prices_
set pagesize 500
alter session set nls_date_format = 'DD.MM.YYYY';

create table prices_ (
  sku        varchar2(38),
  price      number,
  valid_from date
);

insert into prices_ values ('4711', 18, '08.01.2003');
insert into prices_ values ('4711', 19, '01.05.2000');
insert into prices_ values ('4711', 20, '18.01.2001');
insert into prices_ values ('4711', 21, '09.01.2000');

insert into prices_ values ('beer', 14, '07.03.2000');
insert into prices_ values ('beer', 15, '10.01.2003');
insert into prices_ values ('beer', 16, '18.01.2001');
insert into prices_ values ('beer', 19, '16.11.2001');
insert into prices_ values ('beer', 17, '19.02.2002');


create view prices_today_ as 
  select 
    sku,
    price,
    valid_from
  from
    (select
       sku,
       price,
       valid_from,
       rank() over (partition by sku order by valid_from desc) r
     from
       prices_
    )
  where r=1;


select * from prices_today_;

drop table prices_;
drop view prices_today_;