Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 497F59F9F46 for ; Fri, 15 Dec 2006 09:01:03 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 01465-04 for ; Fri, 15 Dec 2006 09:00:56 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mx2.ipf.is (scania.ipf.is [85.197.192.147]) by postgresql.org (Postfix) with ESMTP id C1E369FA2CA for ; Fri, 15 Dec 2006 09:00:56 -0400 (AST) Received: (qmail 2696 invoked by uid 1010); 15 Dec 2006 12:57:52 +0000 Received: from 88.149.106.19 by scania (envelope-from , uid 1008) with qmail-scanner-1.25-st-qms (clamdscan: 0.88/1235. spamassassin: 3.1.1. perlscan: 1.25-st-qms. Clear:RC:1(88.149.106.19):SA:0(-2.0/4.5):. Processed in 4.02014 secs); 15 Dec 2006 12:57:52 -0000 X-Antivirus-IPF-Mail-From: gnari@hive.is via scania X-Antivirus-IPF: 1.25-st-qms (Clear:RC:1(88.149.106.19):SA:0(-2.0/4.5):. Processed in 4.02014 secs Process 2689) Received: from dsl-149-106-19.hive.is (HELO ?192.168.1.33?) (88.149.106.19) by mx2.ipf.is with SMTP; 15 Dec 2006 12:57:47 +0000 Subject: Re: Need to subtract values between previous and current row From: Ragnar To: Partha Guha Roy Cc: pgsql-sql@postgresql.org In-Reply-To: <6bdfe880612150427o19bfe90ch87a972c0f5898ffb@mail.gmail.com> References: <6bdfe880612150427o19bfe90ch87a972c0f5898ffb@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1 Date: Fri, 15 Dec 2006 13:01:50 +0000 Message-Id: <1166187710.6369.164.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.8.1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/110 X-Sequence-Number: 27066 On fös, 2006-12-15 at 18:27 +0600, Partha Guha Roy wrote: > Hi, > > I have a table that contains data like this: > > ID ATIME (MM/dd/yyyy) > == ==== > 1 10/12/2006 > 2 10/14/2006 > 3 10/18/2006 > 4 10/22/2006 > 5 10/30/2006 > > Now I need a query that will subtract atime of row 1 from row 2, row2 > from row3 and so on... if there are no missing IDs you can use a self join SELECT t1.atime,t2,atime FROM t AS t1 JOIN t AS t2 ON (t1.ID=t2.ID+1) otherwise, you could use a loop in a pl/pgpsql function gnari