From: Ragnar <gnari@hive.is>
To: Partha Guha Roy <partha.guha.roy@gmail.com>
Cc: Richard Broersma Jr <rabroersma@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Need to subtract values between previous and current row
Date: Fri, 15 Dec 2006 13:40:25 +0000
Message-ID: <1166190025.6369.169.camel@localhost.localdomain> (raw)
In-Reply-To: <6bdfe880612150521n137d80f7q463955f26db1cef0@mail.gmail.com>
References: <6bdfe880612150427o19bfe90ch87a972c0f5898ffb@mail.gmail.com>
<297264.3730.qm@web31806.mail.mud.yahoo.com>
<6bdfe880612150521n137d80f7q463955f26db1cef0@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gnari@hive.is, partha.guha.roy@gmail.com, rabroersma@yahoo.com
Subject: Re: Need to subtract values between previous and current row
In-Reply-To: <1166190025.6369.169.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox