Hi, 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. Thanks. On 12/15/06, Richard Broersma Jr <[EMAIL PROTECTED]> wrote:
> > ID ATIME (MM/dd/yyyy) > == ==== > 1 10/12/2006 > 2 10/14/2006 > 3 10/18/2006 > 4 10/22/2006 > 5 10/30/2006 > > Now I need a query that will subtract atime of row 1 from row 2, row2 from > row3 and so on... just an idea. select (A.atime - max(B.atime)) duration from table A join table B on (A.atime > B.atime) group by A.atime; Regards, Richard Broersma Jr.
-- ------------------------------ Partha Guha Roy *********************************************** Take everything you like seriously, except yourselves. - Rudyard Kipling