To: "Viorel Dragomir" <bigchief@vio.ro>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: summing tables
References: <20030715140921.7c5a6a6c.erik@thiele-hydraulik.de>
	<001501c34aca$e70ca280$0600a8c0@fix.ro>
	<20030715143925.54716ed4.erik@thiele-hydraulik.de>
	<007101c34ad6$3e0249e0$0600a8c0@fix.ro>
In-Reply-To: <007101c34ad6$3e0249e0$0600a8c0@fix.ro>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 15 Jul 2003 10:55:38 -0400
Message-ID: <878yqzn8k5.fsf@stark.dyndns.tv>
Lines: 22
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


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