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)

GMB · Accepted Answer · 2020-03-05 22:39:17Z

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

answered Mar 5, 2020 at 22:39

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-03-05 22:40:16Z

1

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;

answered Mar 5, 2020 at 22:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Query Postgres in two table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related