Message-ID: <010201c34ae2$b35b3830$0600a8c0@fix.ro>
From: "Viorel Dragomir" <bc@vio.ro>
To: "Greg Stark" <gsstark@mit.edu>
Cc: <pgsql-sql@postgresql.org>
References: 
 <20030715140921.7c5a6a6c.erik@thiele-hydraulik.de><001501c34aca$e70ca280$0600a8c0@fix.ro><20030715143925.54716ed4.erik@thiele-hydraulik.de><007101c34ad6$3e0249e0$0600a8c0@fix.ro>
	<878yqzn8k5.fsf@stark.dyndns.tv>
Subject: Re: summing tables
Date: Tue, 15 Jul 2003 18:06:42 +0300
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

The primary problem was that the update command doesn't modify rows in the
order u want to do it.
I think the update starts with the latest inserted rows. I guess.

Anyway, in real life this update modifies only one row with a value wich is
diff of null.
It was really handy if it was specified the option ORDER for the update
command.

----- Original Message -----
From: "Greg Stark" <gsstark@mit.edu>
To: "Viorel Dragomir" <bigchief@vio.ro>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, July 15, 2003 5:55 PM
Subject: Re: [SQL] summing tables


>
> To solve this problem efficiently you probably need the lead/lag analytic
> functions. Unfortunately Postgres doesn't have them.
>
> You could do it with something like:
>
> update foo set c = a+b+(select c from foo as x where seq < foo.seq ORDER
BY seq desc LIMIT 1)
>
> or the more standard but likely to be way slower:
>
> update foo set c = a+b+(select c from foo as x where seq = (select
max(seq) from foo as y where seq < foo.seq))
>
>
> However, i would suggest that if you have an implicit relationship between
> records you should make that relationship explicit with a foreign key. If
you
> had a column that contained the seq of the parent record then this would
be
> easy. I'm really puzzled how this query as currently specified could be
> useful.
>
>
> --
> greg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster