how do i get old value and new value from audit table

Question

Hi am working on audit data, I have the following data in a table.

colm1  colm2   colm3  

f1      50     4/5/2017 3.38

f1      70     4/5/2017 3.40

f1      80     5/5/2017 3.40

f1      30     5/5/2017 5.40

I would like to show when and what was changed in a table desired output

colm1   oldvalue  newvalue  date 

f1      50         70       4/5/2017

f1      70         80       5/5/2017

f1      80         30       5/5/2017

Answer 1

You can do this using lag():

select a.colm1, a.colm2 as newvalue,
       lag(colm2) over (partition by colm1 order by colm3) as prevvalue,
       colum3
from audit a;

If you want only three rows:

select a.*
from (select a.colm1, a.colm2 as newvalue,
             lag(colm2) over (partition by colm1 order by colm3) as prevvalue,
             colum3
      from audit a
     ) a
where prevvalue is not null;