On Dec 16, 8:00 am, ddf <orat...@msn.com> wrote:
> On Dec 16, 6:15 am, Rem-8 <lukasz.r...@gmail.com> wrote:
>
>
>
>
>
> > Hello ddf.
>
> > Lag function was perfect. I was reading all day about it and it's a
> > perfect solution for me. When I have one row of data I can sort it by
> > CR_DT, as you stated, but let's assume I have few columns like:
>
> > CR_DT COUNTRY STATE CITY CUM_SALES INCR_SALES
>
> > Each sales person is in one table with CUM_SALES values in it. Each
> > person is from countr, state and city. When records will be ordered by
> > CR_DT, only the newest entry, regardless of the rest, will be caught
> > into lag function. When I order lag by CR_DT, COUNTRY, STATE and CITY
> > (in that order) it would also give me wrong last row as it will take
> > the lowest/highest (depending on sort order) value from last column
> > ordered (here is CITY). Can this lag function work for columns which
> > are only the same? So INCR_SALES would count subtract from two New
> > Yorks ordered by date :) This would accomplish my whole task and I
> > will buy a beer for proper solution :D
>
> You'll need to provide some sample data before anyone can positively
> answer that question.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
I was looking through my stash of examples and remember one I recently
posted here; possibly it is what you're wanting:
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
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
