Re: Need to subtract values between previous and current row - Mailing list pgsql-sql

> 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

select A.cid, (A.atime - max(B.atime)) duration, A.state
from table A join table B
on (A.atime > B.atime and A.cid = B.cid)
group by A.atime, A.cid, A.state;