Re: Update table with max occurance from another table

Ok, thank you, this seems to work. I do not undertand what the 'from b as c'
syntax though, much less why its necessary. At any rate, my thanks to you.

"Stephan Szabo" <sszabo(at)megazone23(dot)bigpanda(dot)com> wrote in message
news:20021119110924(dot)D68336-100000(at)megazone23(dot)bigpanda(dot)com(dot)(dot)(dot)
> On Tue, 19 Nov 2002, Dan Winslow wrote:
>
> > And given the following task :
> >
> > update a from b such that a.maxtype is set equal to the b.type whose val
> > number is the highest for that matching id, that is, the result :
>
> As a starting point, not using the postgresql extensions, or any thought
> to make it more efficient, maybe something like:
>
> update a set maxtype=(select type from b where b.id=a.id and
> b.val=(select max(val) from b as c where c.id=b.id));
>
> I think using postgres extensions, you could do this as:
> update a set maxtype=b.type from (select distinct on (id) id, type
> from b order by id, val desc) as b where a.id=b.id;
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org