select id, price, date_modified
from prices
where id, date_modified in (
select id, max(date_modified)
from prices
group by id
)
А если у вас Oracle, то можно без явного подзапроса:
select distinct id, first_value(price) over (partition by id order by date_modified desc)
from prices
