From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Dan Winslow <d(dot)winslow(at)cox(dot)net> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Update table with max occurance from another table |
Date: | 2002-11-19 20:12:37 |
Message-ID: | 20021119121123.Y68992-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel C. Wickstrom | 2002-11-19 20:19:14 | ANNOUNCE: OpenFTS release 0.3.2 |
Previous Message | Dan Winslow | 2002-11-19 19:53:26 | Re: Update table with max occurance from another table |