Re: Need to subtract values between previous and current row

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