Find columns where change happens

Question

I am pretty basic in my sql code, but I am trying to find a way to decide when a change occurs in a set of data.

I have a 3 columns where column 1 (AgreementNr) can have many records. Each of these records are associated with a Sysnr after an invoice is produced. The AgreementNr also have a Name connected to it. This Name will usually be the same from Sysnr to Sysnr, but occationally that Name can change.

I am trying to find an elegant way to extract out the instances where such a change occurs. In the below table there are 3 sets of Agreementnr (colormarked) , where each has a set of different Sysnr. What I looking for is only those that the change in name occurs, marked with dots and extracted as a separate table at the bottom. My problem seem to be the SysNr which is different in each instance, but I need this to be able to pinpoint where change happened.

All info is in one table.

Is there any way I can achieve this easily?

Answer 1

You can detect that the name changes by comparing the enumeration of agreementnr with respect to sysnr, and the enumeration of agreementnr, name with respect to sysnr. Then pick the min sysnr for each such group:

select agreementnr, min(sysnr), name, grp
from (  
    select agreementnr, sysnr, name
         , row_number() over (partition by agreementnr 
                              order by sysnr)
         - row_number() over (partition by agreementnr, name 
                              order by sysnr) as grp
    from T
) as x
group by agreementnr, name, grp

You can remove grp from the select, it's only there for demonstration.