acidzombie24 acidzombie24 - 1 year ago 75
SQL Question

Get row filtering by max(col) in postgresql

I'm using postgresql and I'm unfamiliar with it. This code works but I was wondering if I could write it in a more straightforward way. Here I join bar to join bar in a subquery. I was hoping there's something simple like

select * from bar group by baz using max(z)

select *
from foo f
join bar b on(f.baz=b.baz AND b.z in (select max(z) from bar group by baz))
where uid1 = 120

Answer Source

Just use distinct on:

select distinct on (f.baz) *
from foo f join
     bar b
     on f.baz = b.baz 
where uid1 = 120
order by f.baz, b.z desc;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download