Join only the last row of an associated table with PostgreSQL
postgresql, sql
Solution
I think this one is simpler and faster
select
id,
p.ano_processo,
p.numero_processo,
h.descricao,
h.status,
h.data_modificacao
from
vs_protocolo p
inner join
(
select distinct on (id_protocolo)
id_protocolo as id,
descricao,
status,
data_criacao as data_modificacao
from vs_protocolo_historico
order by id_protocolo, data_criacao desc
) h using (id)
Problem
Given the folowing tables: ``` Table "public.vs_protocolo" Column | Type | Modifiers -----------------+-----------------------------+----------------------------------------------------------- id | integer | not null default nextval('vs_protocolo_id_seq'::regclass) data_criacao | timestamp without time zone | not null default now() ano_processo | integer | not null numero_processo | integer | not null Table "public.vs_protocolo_historico" Column | Type | Modifiers --------------+-----------------------------+--------------------------------------------------------------------- id | integer | not null default nextval('vs_protocolo_historico_id_seq'::regclass) id_protocolo | integer | not null descricao | character varying(255) | not null status | integer | not null default 0 data_criacao | timestamp without time zone | not null default now() ``` I must select all rows from `vs_protocolo` joined with the last row from `vs_protocolo_historico`. I'm worring about performance, so it must avoid sub-queries or, at least, avoid subqueries for every row at `vs_protocolo`. Note: `vs_protocolo_historico(id_protocolo) REFERENCES vs_protocolo(id)`.