Query Postgres in two table

Question

I need to bring the result of a consultation.

I need to bring the latest version of each inst_code, as well as its name (table B)

Answer 1

In Postgres, you can use distinct onto solve this top-1-per-group problem:

select distinct on(a.inst_code)
    a.inst_code,
    b.inst_name,
    a.version,
    a.status,
    a.date
from tablea a
inner join table b on b.inst_code = a.inst_code
order by a.inst_code, a.version desc, a.date desc

Answer 2

I think you can use distinct on:

select distinct on (inst_code) *
from b join
     a
     using (inst_code)
order by inst_code, version desc;