Re: Update table with max occurance from another table

On Tue, 19 Nov 2002, Dan Winslow wrote:

> 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.

Just to answer this, it's saying from the table b aliased under the name c
because I want the name b to refer to the outer b inside the sub-subquery
so I can say c.id=b.id.  I could probably have just done b.id=a.id there
as well, but it felt easier that way.

> "Stephan Szabo" <sszabo@megazone23.bigpanda.com> wrote in message
> news:20021119110924.D68336-100000@megazone23.bigpanda.com...
> > 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@postgresql.org
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>