On Nov 19, 5:24 pm, myk <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I have a table that contains two columns, USER and VALUE. For each
> user there are multiple values. What I would like to do is, after
> ordering the list by user and then value, subtract each value from the
> next value and place this new data-element into a third column named
> DIFF.
>
> Example:
>
> DATA AS IS:
> USER VALUE
> 1 7
> 1 9
> 1 11
> 1 13
> 2 5
> 2 6
> 2 7
>
> DATA AS I WOULD LIKE TO GET IT:
> USER VALUE DIFF
> 1 7 -
> 1 9 2
> 1 11 2
> 1 15 4
> 2 5 -
> 2 8 3
> 2 15 7
>
> Anyone know how to do this in SQL or PL*SQL?
>
> TIA!
>
> myk
You need to use the LAG function, and no PL/SQL is involved:
SQL> create table data(
2 usr number,
3 value number
4 );
Table created.
SQL>
SQL> insert all
2 into data
3 values (1,7)
4 into data
5 values (1,9)
6 into data
7 values (1,11)
8 into data
9 values (1,15)
10 into data
11 values (2,5)
12 into data
13 values (2,8)
14 into data
15 values (2,15)
16 select * From dual;
7 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select * From data;
USR VALUE
---------- ----------
1 7
1 9
1 11
1 15
2 5
2 8
2 15
7 rows selected.
SQL>
SQL> select usr, value,
2 case when usr = prev_usr then value - lag(value) over
(order by usr, value) end diff
3 from
4 (select usr, value, lag(usr) over (order by usr) prev_usr
5 from data);
USR VALUE DIFF
---------- ---------- ----------
1 7
1 9 2
1 11 2
1 15 4
2 5
2 8 3
2 15 7
7 rows selected.
SQL>
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
