with prices(day,price,prod,clr) as (
  select 1, 3.99, 'Apple', 'Green' from dual union all
  select 1, 2.49, 'Apple', 'Red'   from dual union all
  select 1, 5.99, 'Pear',  'Small' from dual union all
  select 2, 3.79, 'Apple', 'Green' from dual union all
  select 3, 3.69, 'Apple', 'Green' from dual
)
select prod,clr, ???? as LastUpdate, ???? as LastPrice from prices
 group by prod,clr

-- Простой и медленный: отфильтровать все старые цены
select * from prices p 
 where day=(select max(day) from prices t 
             where (p.prod,p.clr)=((t.prod,t.clr)))

-- через селф-джойн:
select p.* from prices p
  join (select max(day) LastDay, prod, clr from prices group by prod, clr) t 
      on (t.LastDay,t.prod,t.clr) = ((p.day,p.prod,p.clr))

-- через ранг, но теперь лишний столбец появился
select * 
  from (select p.*, dense_rank() over (partition by prod,clr order by day desc) rnk from prices p)
 where rnk = 1

-- аналитическими функциями, то тогда на каждый столбец - одинаковая долгая писанина
select distinct
  max(day)   keep (dense_rank last order by day) over (partition by prod,clr) as LastDay,
  max(price) keep (dense_rank last order by day) over (partition by prod,clr) as LastPrice,
  prod, clr 
  from prices

-- ну или так, но тоже писанина
select 
  max(day)   keep (dense_rank last order by day) as LastDay,
  max(price) keep (dense_rank last order by day) as LastPrice,
  prod, clr 
  from prices
 group by prod, clr

select
  t2.*
from
  (  
    select
      id,
      min(rowid) "rowid" 
    from
      ttt 
    group by 
      id
  ) t1,
  ttt t2
where
  t1."rowid" = t2.rowid

create table prices(day integer, price number, prod varchar2(10), clr varchar2(10) );
insert into prices select * from (
  select 1, 3.99, 'Apple', 'Green' from dual union all
  select 1, 2.49, 'Apple', 'Red'   from dual union all
  select 1, 5.99, 'Pear',  'Small' from dual union all
  select 2, 3.79, 'Apple', 'Green' from dual union all
  select 3, 3.69, 'Apple', 'Green' from dual
);

select p.* 
  from (select max(rowid) keep (dense_rank last order by day) as therow from prices group by prod,clr) t
  join prices p on t.therow=p.rowid;

with prices(day,price,prod,clr) as (
  select 1, 3.99, 'Apple', 'Green' from dual union all
  select 1, 2.49, 'Apple', 'Red'   from dual union all
  select 1, 5.99, 'Pear',  'Small' from dual union all
  select 2, 3.79, 'Apple', 'Green' from dual union all
  select 3, 3.69, 'Apple', 'Green' from dual
)
select prod,clr, MAX(price) keep (dense_rank last order by day) as therow from prices group by prod,clr