T-sql Reset Row number on Field Change

Question

Similar to a recent post of mine "t-sql sequential duration"”, but not exactly the same, I want to reset the row number based on a change in column x (in my case, column "who"). Here's the first query that returns the a small sample of the raw(ish) data: ``` SELECT DISTINCT chr.custno, CAST(LEFT(CONVERT( VARCHAR(20),chr.moddate,112),10)+ ' ' + chr.modtime AS DATETIME)as moddate, chr.who FROM <TABLE> chr WHERE chr.custno = 581827 AND LEFT(chr.who, 5) = 'EMSZC' AND chr.[description] NOT LIKE 'Recalled and viewed this customer' ORDER BY chr.custno ``` Result: ``` custno moddate who 581827 2012-11-08 08:38:00.000 EMSZC14 581827 2012-11-08 08:41:10.000 EMSZC14 581827 2012-11-08 08:53:46.000 EMSZC14 581827 2012-11-08 08:57:04.000 EMSZC14 581827 2012-11-08 08:58:35.000 EMSZC14 581827 2012-11-08 08:59:13.000 EMSZC14 581827 2012-11-08 09:00:06.000 EMSZC14 581827 2012-11-08 09:04:39.000 EMSZC49 Reset row number to 1 581827 2012-11-08 09:05:04.000 EMSZC49 581827 2012-11-08 09:06:32.000 EMSZC49 5…

Accepted Answer

If you are on SQL Server 2012 you can use LAG to compare value with previous row and you can use SUM and OVER to record the changes.

```
with C1 as
(
  select custno,
         moddate,
         who,
         lag(who) over(order by moddate) as lag_who
  from chr
),
C2 as
(
  select custno,
         moddate,
         who,
         sum(case when who = lag_who then 0 else 1 end) 
            over(order by moddate rows unbounded preceding) as change 
  from C1
)
select row_number() over(partition by change order by moddate) as RowID,
       custno,
       moddate,
       who
from C2
```

SQL Fiddle

Update:

A version for SQL Server 2005. It uses a recursive CTE and a temp table for intermediary storage of the data you need to iterate over.

```
create table #tmp
(
  id int primary key,
  custno int not null,
  moddate datetime not null,
  who varchar(10) not null
);

insert into #tmp(id, custno, moddate, who)
select row_number() over(order by moddate),
       custno,
       moddate,
       who
from chr;

with C as
(
  select 1 as rowid,
         T.id,
         T.custno,
         T.moddate,
         T.who,
         cast(null as varchar(10)) as lag_who
  from #tmp as T
  where T.id = 1
  union all
  select case when T.who = C.who then C.rowid + 1 else 1 end,
         T.id,
         T.custno,
         T.moddate,
         T.who,
         C.who
  from #tmp as T
    inner join C
      on T.id = C.id + 1
)
select rowid,
       custno,
       moddate,
       who
from C
option (maxrecursion 0);

drop table #tmp;
```

SQL Fiddle

T-sql Reset Row number on Field Change

Solution

Problem