Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 43B769FA173 for ; Fri, 15 Dec 2006 09:39:34 -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 11729-04 for ; Fri, 15 Dec 2006 09:39:28 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mx1.ipf.is (mack.ipf.is [85.197.192.164]) by postgresql.org (Postfix) with ESMTP id 134029F9B05 for ; Fri, 15 Dec 2006 09:39:28 -0400 (AST) Received: (qmail 31269 invoked by uid 1010); 15 Dec 2006 13:36:05 +0000 Received: from 88.149.106.19 by mack (envelope-from , uid 1008) with qmail-scanner-1.25-st-qms (clamdscan: 0.88.4/1639. spamassassin: 3.1.4. perlscan: 1.25-st-qms. Clear:RC:1(88.149.106.19):SA:0(-2.4/4.5):. Processed in 0.361959 secs); 15 Dec 2006 13:36:05 -0000 X-Antivirus-HIVE-Mail-From: gnari@hive.is via mack X-Antivirus-HIVE: 1.25-st-qms (Clear:RC:1(88.149.106.19):SA:0(-2.4/4.5):. Processed in 0.361959 secs Process 31260) Received: from dsl-149-106-19.hive.is (HELO ?192.168.1.33?) (88.149.106.19) by mx1.ipf.is with SMTP; 15 Dec 2006 13:36:05 +0000 Subject: Re: Need to subtract values between previous and current row From: Ragnar To: Partha Guha Roy Cc: Richard Broersma Jr , pgsql-sql@postgresql.org In-Reply-To: <6bdfe880612150521n137d80f7q463955f26db1cef0@mail.gmail.com> References: <6bdfe880612150427o19bfe90ch87a972c0f5898ffb@mail.gmail.com> <297264.3730.qm@web31806.mail.mud.yahoo.com> <6bdfe880612150521n137d80f7q463955f26db1cef0@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1 Date: Fri, 15 Dec 2006 13:40:25 +0000 Message-Id: <1166190025.6369.169.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/115 X-Sequence-Number: 27071 On fös, 2006-12-15 at 19:21 +0600, Partha Guha Roy wrote: > Thanks for everyones email. Let me clarify a little bit more. > > Actually there is a state change of an entity. now the results are > stored in the following manner: > > CID ATIME STATE > 101 12/10/2006 1 > 101 12/12/2006 2 > 101 12/14/2006 1 > 101 12/17/2006 2 > 102 12/14/2006 1 > 102 12/16/2006 2 > 102 12/18/2006 3 > > Now I have to find which CID stayed on which STATE for how long. > > Lets assume today is the 20th. > So the result would be: > > CID DURATION STATE > 101 2 1 > 101 2 2 > 101 3 1 > 101 3 2 > 102 2 1 > 102 2 2 > 102 2 3 > > The constraints are: > I can't use any plpgsql or other function. > There can be gaps in CID. > No sequence can be created. those are constraints indeed. what do you mean by 'can't use any plpgsql or other function'? no user-defined functions, or no functions at all can you use temp tables ? what version of postgres is this? actually, this looks like a classical client-side problem. gnari