DB2 Get latest modified and previous value from audit table

Question

I have a audit table, i am trying to get the current and previous value for a column(rank) with audit timestamp information. I would like to get the timestamp when the value was changed. E.g:

For id = 1, rank was latest changed from 3 to 5 on 13-05-2021 14:10 by userid = 2. I have written below query it gives the current and previous modified value but it gives the latest date and userid (17-05-2021 20:00 and 2), because row_number is ordered by timestamp.

with v_rank as (    
    select * from (
        select
            id,
            a.rank as current_rank,
            b.rank as previous_rank,
            a.log_timestamp,
            a.log_username,
            row_number() over(partition by a.id order by a.log_timestamp) as rnum
        from
            user a
            inner join user b on a.id = b.id and a.log_timestamp > b.timestamp
            where
                a.rank != b.rank
            order by a.log_timestamp, b.timestamp
    ) where rnum = 1
)
select * from v_rank

Any suggestion on how can i get the correct timestamp(13-05-2021 14:10) and userid(2). Edit: Rank can also be null, in that case i need to get the blank in query result.

Expected output:

Gordon Linoff Gordon Linoff · Accepted Answer · 2021-05-17T18:20:57

You seem to want lag() with filtering:

select u.*
from (select u.*,
             lag(rank) over (partition by id order by log_timestamp) as prev_rank
      from user u
     ) u
where rank <> prev_rank;

DB2 Get latest modified and previous value from audit table

1 Answers