Re: Need to subtract values between previous and current row - Mailing list pgsql-sql

From Ragnar
Subject Re: Need to subtract values between previous and current row
Date
Msg-id 1166190025.6369.169.camel@localhost.localdomain
Whole thread Raw
In response to Re: Need to subtract values between previous and current row  ("Partha Guha Roy" <partha.guha.roy@gmail.com>)
List pgsql-sql
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




pgsql-sql by date:

Previous
From: "Partha Guha Roy"
Date:
Subject: Re: Need to subtract values between previous and current row
Next
From: Richard Broersma Jr
Date:
Subject: Re: Need to subtract values between previous and current row