Compare previous revisions of a column

Question

I need to identify differences from various revisions for each item below, how can I compare data in the same column - Value based on older revisions?

T1:
ID    Value   Rev    
1     1001     01    
1     1002     02    
1     1001     03    
1     1002     03    
2     1003     02    
2     1004     03    
3     1003     01    
3     1005     02    
3     1005     03  
4     1002     01

For #1: Rev 1 - 1001, Rev 2- 1002, Rev 3 - 1001 & 1002
For #2: Rev 2 - 1003, Rev 3 - 1004
For #3: Rev 1 - 1003, Rev 2: 1005

Desired Output:

ID    Value    Rev
1     1001     01         
1     1002     02        
1     1001     03         
1     1002     03         
2     1003     02         
2     1004     03         
3     1003     01          
3     1005     02

You can join your table with itself. Take a look at https://stackoverflow.com/questions/13708176/find-which-rows-have-different-values-for-a-given-column-in-teradata-sql — Rick Grimes, Jul 19 '19 at 05:45

Fahmi · Accepted Answer · 2019-07-19T05:50:35.753

3

you can try using lag() and lead()

DEMO

select id, value, rev from 
(
select * , lead(value) over(partition by id order by rev) as val1,
lag(value) over(partition by id order by rev) as val2
from t1
)A 
where (value<>val2 or value<>val1)

OUTPUT:

id  value   rev
1   1001    01
1   1002    02
1   1001    03
1   1002    03
2   1003    02
2   1004    03
3   1003    01
3   1005    02

edited Jul 19 '19 at 05:50

answered Jul 19 '19 at 05:42

Fahmi

37,315
5
22
31

Thank you! how do I only identify the revision where the change occurred? This seems to be returning both the old and the new revisions. – Skn Jul 23 '19 at 17:47

score 0 · Answer 2 · answered Jul 19 '19 at 06:06

You can simply do self-join and use Distinct for achieving the result as following:

SELECT DISTINCT T11.ID, T11.VALUE, T11.rev FROM 
T1 T11 
JOIN T1 T12 
ON (T11.ID = T12.ID AND T11.VALUE <> T12.VALUE)
ORDER BY T11.ID, T11.rev

db<>fiddle demo

Cheers!!

score 0 · Answer 3 · answered Jul 19 '19 at 11:58

0

I think you want ids that have more than one row. If so:

select t.*
from (select t.*, count(*) over (partition by id) as cnt
      from t
     ) t
where cnt > 1
order by id, rev;

Or use exists:

select t.*
from t
where exists (select 1
              from t tt
              where tt.id = t.id and
                    (tt.value <> t.value or
                     tt.rev <> t.rev
                    )
             )
order by id, rev;

answered Jul 19 '19 at 11:58

Gordon Linoff

1,242,037
58
646
786

No, actually I want to compare the revisions for each I’d and identify changes. Eg- #1 has 1001 in the first revision, 1002 in the 2nd and both 1001 and 1002 in the 3rd revision. – Skn Jul 19 '19 at 14:04

Compare previous revisions of a column

3 Answers3