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