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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
