On Sun, May 11, 2008 at 11:07 PM, Mag Gam <
magawake@gmail.com> wrote:
Hi All,
I have a view that generates output similar to this.
select * from foo.view;
ts | size
-------------------+-----
2002-03-16 | 11
2002-03-17 | 16
2002-03-18 | 18
2002-03-19 | 12
I am trying to find the difference between the size column. So the desired output would be
ts | size| Diff
-------------------+-----+------
2002-03-16 | 11 | 0
2002-03-17 | 15 | 4
2002-03-18 | 18 | 3
2002-03-19 | 12 | -6
I need the first column to be 0, since it will be 11-11. The second colum is 15-11. The third column is 18-15. The fourth column is 12-18.
Any thoughts about this?
Try this:
select ts,
size,
t1.size - (select t2.size
from foo.view as t2
where t2.ts < t1.ts
order by ts desc
limit 1) as diff
from foo.view as t1
order by ts asc;
HTH,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB
http://www.enterprisedb.comMail sent from my BlackLaptop device