Analytic Function da Oracle

[Motta 645]

Existe alguma Analytic Function da Oracle que returne o valor de uma série baseada em outra coluna

 

Algo assim

Formecedor Data     Preço
ABC        2/ABR/15 123
ABC        3/ABR/15 125
ABC        5/ABR/15 111
XYZ        7/ABR/15 101
XYZ        9/ABR/15 101
qaz        3/ABR/15 99

Eu preciso o preço ma data mais recente (Usando Analytic Functions)

ABC 111
XYZ 101
qaz 99

Valeu

[Motta 645]

Solução do Michal Simonik (via Linkedin)

• 
  

  create table suppliers
  (
  supplier_id varchar2(3),
  price_date date,
  price number(5,2)
  ) tablespace users;
  
  insert into suppliers values ('ABC',to_date('02042014','ddmmyyyy'),123);
  insert into suppliers values ('ABC',to_date('03042014','ddmmyyyy'),125);
  insert into suppliers values ('ABC',to_date('05042014','ddmmyyyy'),111);
  insert into suppliers values ('XYZ',to_date('06042014','ddmmyyyy'),101);
  insert into suppliers values ('XYZ',to_date('07042014','ddmmyyyy'),101);
  insert into suppliers values ('qaz',to_date('03032014','ddmmyyyy'),99);
  commit;
  
  select distinct supplier_id, first_value(price) over (partition by supplier_id order by price_date desc) price from suppliers;
  
  table SUPPLIERS created.
  1. rows inserted.
  
  1. rows inserted.
  
  1. rows inserted.
  
  1. rows inserted.
  
  1. rows inserted.
  
  1. rows inserted.
  
  commited.
  SUPPLIER_ID PRICE
  * -----
  
  qaz 99
  ABC 111
  XYZ 101
  
  
  Reference:
  http://docs.oracle.com/cd/E11882_01/server.112/e25554/analysis.htmhttp://oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions.php