Re: sql - statement: building differences between data-sets

rudi.ramstein_at_gmx.de (beyond) wrote in message news:<621ad8ac.0408031217.41fc6fc3_at_posting.google.com>...
> i need a sql-statement for this problem
>
> i ve got a table like this
> ID;value
> 1;30
> 2;34
> 3;44
>
> the result of sql-statement should calculate differences to
> previous/other datasets like this
> ID;diff
> 1; 30-0
> 2; 34-30
> 3; 44-34
>
> who can solve this problem elegant?

When a newsgroup has subgroups you should generally post to the subgroups (.server, .misc, .tools, and .marketplace) rather than to the group.

Join the table to itself on the key value - 1

select a.id, a.value, b.id, b.value, nvl(b.value,0) - nvl(a.value,0) as Diff
from marktest3 a,

      (select (c.id - 1) AS id, c.value
       from   marktest3 c
       ) b

        ID VALUE ID VALUE DIFF ---------- ---------- ---------- ---------- ----------

                               0         30         30
         1         30          1         34          4
         2         34          2         44         10

There are other ways to accomplish the same output.

HTH -- Mark D Powell -- Received on Wed Aug 04 2004 - 16:13:18 CEST