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?

enter image description here


1 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.

  • Yes! This seems to work all nicely! I need to run a few controls on bigger datasets, but on a few random sample it seems to give exactly what I am looking for! Thanks alot! :)
    – Gunsto
    Commented Sep 29, 2022 at 18:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.