Is it possible to remake a query without using subqueries?

Question

PostgreSQL

create table test
(
  id bigserial primary key,
  price_id integer not null,
  value integer not null,
  time_action timestamp with time zone
)

select distinct price_id, (select value from test t2 where t1.price_id=t2.price_id order by time_action desc limit 1)  from test t1

The essence of the task is to pull out all the unique price_id from the test table with the latest value. Can this be done somehow without subqueries to speed up the query?

Answer 1

select distinct on (price_id), price_id,value  from tablename order by price_id, time_action desc

If there are many lines for each price_id, then you are wrong in thinking that the request slows down.

select price_id, value from prices as t1 lateral (select value from test t2 where t1.id=t2.price_id order by time_action desc limit 1) on true;

If there is no prices table, then build a loose index scan manually.
All of course with a suitable index.