1

Consider a table like the following:

col1  col2  col3

1000  RRR   100000
2000  RRR   400000
3000  RRR   300000
4000  YYY   200000
5000  YYY   400000
6000  RRR   400000
6000  ZZZ   500000

The output I want:

1000  RRR  0
2000  RRR  300000
3000  RRR  100000
6000  RRR  400000
....

I need to perform partition on col2, order by on col3 and then find difference on rows.

Lyashko Kirill
  • 513
  • 3
  • 14
Bijendra Kumar
  • 11
  • 1
  • Hello Bijendra, nice to see you in StackOverflow. Please try to be more descriptive with your problem, provide us with some insights of what are you trying to achieve. Provide us with a working using a plunkr or similar, or at least a piece of code that is usable to reproduce your error. Don't forget to format your question using de ` for code `

    Like this

    `     – ZetaPR Jan 27 '20 at 14:39

1 Answers1

0

You can use this code snippet as a base for your query:

SELECT c1, c2,
       c3 - nvl(lag(c3) OVER (PARTITION BY c2 ORDER BY c1), c3) as diff
FROM t;
Lyashko Kirill
  • 513
  • 3
  • 14
  • 1
    Thanks for your valuable comment.. what you meant by nv1 ?? – Bijendra Kumar Jan 27 '20 at 16:01
  • NVL is a hive function to deal with nulls, in case is there is no previous value http://www.hplsql.org/nvl – Lyashko Kirill Jan 27 '20 at 17:21
  • Yeah Thanks!! Above query didn't serve my result, as my requirement is 1st row of every partition should have same value. Like in R1 C3 value should be same base table. But after using lag value is getting deducted by 1 and respectively in every partition value is getting deducted as offset value....please help.. – Bijendra Kumar Jan 27 '20 at 17:57
  • Can you produce an example what is wrong and how you want to correct it? – Lyashko Kirill Jan 27 '20 at 18:40
  • Hello, here is the sample example. sample table 1,England,10000 2,newyork,20000 3,England,30000 4,Wales,40000 5,England,50000 6,newyork,60000 7,England,70000 8,wales,80000. when i have run the suggested query i got the following result: 1 England 9999 3 England 20000 5 England 20000 7 England 20000 4 Wales 39996 2 newyork 19998 6 newyork 40000 8 wales 79992 Expected Result: 1 England 10000 3 England 20000 4 England 20000 7 England 20000 2 newyork 20000 6 newyork 40000 4 wales 40000 8 wales 40000. – Bijendra Kumar Jan 29 '20 at 14:27
  • if you will notice as my requirement is not to change the 1st row c3 value but 2nd row should have c3(row1-row2) ordered by and so on every partition as i am doing patition by on c2.... – Bijendra Kumar Jan 29 '20 at 14:27