SQL detect change in row

Question

I have data from sql server attached :

select * from log

What I want to do is I want to check if there any changes in code for the column name. So if you see the data from table log, the code change 2 times (B02,B03).

What I want to do is I want to retrieve the row which is the first changes everytime the code change. In this sample, the first changes is on the red box. So I want to have the result for row 5 and row 9.

I've tried to use partition like code below:

select a.name,a.code from(
select name,code,row_number() over(partition by code order by name) as rank from log)a
where a.rank=1

and get result like this.

However, I don't want the first row to be retrieved. Since it is the first value and I don't need that. So i just want to retrieve the changes indicates by column code. Please help if you know how to do it.

and please note, I can't write query using filter where code <> 'B01', because in this case, I don't know what is the first value.

Please assume the first value is the data that first inserted into the table.

Solution 1

create table #log (name nvarchar(100), code nvarchar(100));

insert into #log values ('SARUMA','B01'), ('SARUMA','B01'), ('SARUMA','B01'), ('SARUMA','B01');
insert into #log values ('SARUMA','B02'), ('SARUMA','B02'), ('SARUMA','B02'), ('SARUMA','B02');
insert into #log values ('SARUMA','B03'), ('SARUMA','B03');

-- remove duplicates
with Singles (name, code) 
AS (
    select distinct name, code from #log
),
-- At first you need an order, in time? By alphanumerical code? Otherwise you cannot decide which is the first item you want to remove
-- So I added an identity ordering, but it is preferable to use a physical column
OrderedSingles (name, code, id) 
AS (
    select *, row_number() over(order by name)
    from Singles
)
-- Now self-join to get the next one, if the index is sequential you can join id = id+1
-- and take the join columns
select distinct ii.name, ii.Code
from OrderedSingles i
inner join OrderedSingles ii 
    on i.Name = ii.Name and i.Code <> ii.Code
where i.id < ii.Id;

Solution 2

Use lag to get the previous row's value (assuming id specifies ordering) and get the rows where it is different from the current row's value.

create table #log (id int identity(1,1) not null, name nvarchar(100), code nvarchar(100));

insert into #log(name,code) values ('SARUMA','B01'), ('SARUMA','B01'), ('SARUMA','B01'), ('SARUMA','B01');
insert into #log(name,code)  values ('SARUMA','B02'), ('SARUMA','B02'), ('SARUMA','B02'), ('SARUMA','B02');
insert into #log(name,code)  values ('SARUMA','B03'), ('SARUMA','B03');

select name
    ,code
from (
    select l.*
        ,lag(code) over (
            partition by name order by id
            ) as prev_code
    from #log l
    ) l
where prev_code <> code