sql - statement: building differences between data-sets

[beyond]

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?

[Carlos]

Try Analytic functions.

(As Tom Kyte would say: 'Analytics Rock').

An alternative is a join like this:

select 2.id ID, (2.value - nvl(1.value,0)) diff
from table 1, table 2
where 2.id - 1 = 1.id (+)

Cheers.

Carlos.

ru***********@gmx.de (beyond) wrote in message news:<62**************************@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?

[Mark D Powell]

ru***********@gmx.de (beyond) wrote in message news:<62**************************@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
where a.id(+) = b.id

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 --