Re: Difference in columns

------=_Part_820_24964014.1210528506308
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Sun, May 11, 2008 at 11:07 PM, Mag Gam <magawake@[EMAIL PROTECTED]
> 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]@[EMAIL PROTECTED]
 gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

------=_Part_820_24964014.1210528506308
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Sun, May 11, 2008 at 11:07 PM, Mag Gam &lt;<a
href="mailto:magawake@[EMAIL PROTECTED]
">magawake@[EMAIL PROTECTED]
>&gt; wrote:<br><div
class="gmail_quote"><blockquote class="gmail_quote" style="border-left: 1px
solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Hi All,<br><br>I have a view that generates output similar to
this.<br><br>select * from foo.view;<br><br><pre>       ts          |
size<br>-------------------+-----<br> 2002-03-16        | 11 <br>
2002-03-17        | 16 <br>

 2002-03-18        | 18<br>&nbsp;2002-03-19        | 12 
<br></pre>I am trying to find the difference between the size column. So
the desired output would be<br><br><pre>       ts          | size|
Diff<br>-------------------+-----+------<br> 2002-03-16        | 11  |
0<br>
 2002-03-17        | 15  | 4<br> 2002-03-18        | 18  |
3<br>&nbsp;2002-03-19        | 12  | -6<br></pre>
<br>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.
<br><br>Any thoughts about this?</blockquote><div><br>Try
this:<br><br>select&nbsp;&nbsp; ts,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; size,
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
t1.size - (select
t2.size<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
from foo.view as
t2<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
where t2.ts &lt;
t1.ts<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
order by ts
desc<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
limit 1) as diff<br>
from foo.view as t1<br>order by ts asc;</div></div><br>HTH,<br
clear="all"><br>-- <br>gurjeet[.singh]@[EMAIL PROTECTED]
>singh.gurjeet@[EMAIL PROTECTED]
 | hotmail | indiatimes | yahoo }.com<br><br>EnterpriseDB <a
href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br>
<br>Mail sent from my BlackLaptop device

------=_Part_820_24964014.1210528506308--