On Nov 20, 10:14 am, ddf <[EMAIL PROTECTED]> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
And my original query doesn't work with more than two USR values. The
following corrected code does:
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, value) 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
3 5
3 7 2
3 8 1
3 9 1
USR VALUE DIFF
---------- ---------- ----------
3 11 2
3 15 4
4 5
4 7 2
4 8 1
4 9 1
4 11 2
4 14 3
4 15 1
4 19 4
4 22 3
USR VALUE DIFF
---------- ---------- ----------
4 25 3
4 27 2
4 28 1
4 35 7
26 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 Oracle-PLSQL@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
