> not bad, except you miss the initial state for each cid,
> and I do not see how you get the final duration.
> cid | duration | state
> -+--+---
> 101 |2 | 1
> 101 |2 | 2
> 101 |3 | 1
> 101 |3 | 2
> 102 |2 | 1
> 10
On fös, 2006-12-15 at 06:01 -0800, Richard Broersma Jr wrote:
> > CIDATIME STATE
> > 10112/10/2006 1
> > 10112/12/2006 2
> > 10112/14/2006 1
> > 10112/17/2006 2
> > 10212/14/2006 1
> > 10212/16/2006 2
> > 102
> CIDATIME STATE
> 10112/10/2006 1
> 10112/12/2006 2
> 10112/14/2006 1
> 10112/17/2006 2
> 10212/14/2006 1
> 10212/16/2006 2
> 10212/18/2006 3
select A.cid, (A.atime - max(B.atime)) duration, A.sta
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:
>
> CIDATIME STATE
> 10112/10/2006 1
>
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:
CIDATIME STATE
10112/10/2006 1
10112/12/2006 2
10112/14/2006 1
10112/
>
> ID ATIME (MM/dd/)
> ==
> 110/12/2006
> 210/14/2006
> 310/18/2006
> 410/22/2006
> 510/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 ide
I see 3 solutions.
A) self join
B) define a procedure that return a set of records.
this use only a single table scan on the ordered table
not tested, just the genera idea:
CREATE OR REPLACE FUNCTION foo()
RETURNS SETOF AS
...
DELARE
previous_time int8; --(or whaever da
On Fri, 2006-12-15 at 18:27 +0600, Partha Guha Roy wrote:
> I have a table that contains data like this:
>
> ID ATIME (MM/dd/)
> ==
> 110/12/2006
> 210/14/2006
> 310/18/2006
> 410/22/2006
> 510/30/2006
>
> No
On fös, 2006-12-15 at 18:27 +0600, Partha Guha Roy wrote:
> Hi,
>
> I have a table that contains data like this:
>
> ID ATIME (MM/dd/)
> ==
> 110/12/2006
> 210/14/2006
> 310/18/2006
> 410/22/2006
> 510/30/200
Hi,
I have a table that contains data like this:
ID ATIME (MM/dd/)
==
110/12/2006
210/14/2006
310/18/2006
410/22/2006
510/30/2006
Now I need a query that will subtract atime of row 1 from row 2, row2 from
ro
10 matches
Mail list logo