T-sql Reset Row number on Field Change
Solution 1
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
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;
Solution 2
I had success with this issue by using Rank():
SELECT RANK() OVER (PARTITION BY who ORDER BY custno, moddate) AS RANK
This returned your desired results. I actually found this post trying to solve the same problem.
Solution 3
Instead of:
PARTITION BY custno ORDER BY custno, moddate, who)
try:
PARTITION BY custno, who ORDER BY custno, moddate)
Kris
Updated on July 05, 2022Comments
-
Kris about 2 years
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 581827 2012-11-08 09:12:03.000 EMSZC49 581827 2012-11-08 09:12:38.000 EMSZC49 581827 2012-11-08 09:14:18.000 EMSZC49 581827 2012-11-08 09:17:35.000 EMSZC14 Reset row number to 1
Second step is to add the row number (I didn’t do this in the first query because of the use of the word DISTINCT); so…
WITH c1 AS ( 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' ) SELECT ROW_NUMBER() OVER (PARTITION BY custno ORDER BY custno, moddate, who) AS RowID, custno, moddate, who FROM c1
Result:
RowID custno moddate who 1 581827 2012-11-08 08:38:00.000 EMSZC14 2 581827 2012-11-08 08:41:10.000 EMSZC14 3 581827 2012-11-08 08:53:46.000 EMSZC14 4 581827 2012-11-08 08:57:04.000 EMSZC14 5 581827 2012-11-08 08:58:35.000 EMSZC14 6 581827 2012-11-08 08:59:13.000 EMSZC14 7 581827 2012-11-08 09:00:06.000 EMSZC14 8 581827 2012-11-08 09:04:39.000 EMSZC49 Reset row number to 1 9 581827 2012-11-08 09:05:04.000 EMSZC49 10 581827 2012-11-08 09:06:32.000 EMSZC49 11 581827 2012-11-08 09:12:03.000 EMSZC49 12 581827 2012-11-08 09:12:38.000 EMSZC49 13 581827 2012-11-08 09:14:18.000 EMSZC49 14 581827 2012-11-08 09:17:35.000 EMSZC14 Reset row number to 1
The next step is where I’m stuck: the goal is to reset the RowID to 1 on each change of value in the “who” column. The following code gets an “almost there” result (and it should be noted that I stole/borrowed this code from somewhere, but now I can’t find the website):
WITH c1 AS ( 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' ) , c1a AS ( SELECT ROW_NUMBER() OVER (PARTITION BY custno ORDER BY custno, moddate, who) AS RowID, custno, moddate, who FROM c1 ) SELECT x.RowID - y.MinID + 1 AS Row, x.custno, x.Touch, x.moddate, x.who FROM ( SELECT custno, who, MIN(RowID) AS MinID FROM c1a GROUP BY custno, who ) AS y INNER JOIN c1a x ON x.custno = y.custno AND x.who = y.who
Result:
Row custno moddate who 1 581827 2012-11-08 08:38:00.000 EMSZC14 2 581827 2012-11-08 08:41:10.000 EMSZC14 3 581827 2012-11-08 08:53:46.000 EMSZC14 4 581827 2012-11-08 08:57:04.000 EMSZC14 5 581827 2012-11-08 08:58:35.000 EMSZC14 6 581827 2012-11-08 08:59:13.000 EMSZC14 7 581827 2012-11-08 09:00:06.000 EMSZC14 1 581827 2012-11-08 09:04:39.000 EMSZC49 Reset row number to 1 (Hooray! It worked!) 2 581827 2012-11-08 09:05:04.000 EMSZC49 3 581827 2012-11-08 09:06:32.000 EMSZC49 4 581827 2012-11-08 09:12:03.000 EMSZC49 5 581827 2012-11-08 09:12:38.000 EMSZC49 6 581827 2012-11-08 09:14:18.000 EMSZC49 14 581827 2012-11-08 09:17:35.000 EMSZC14 Reset row number to 1 (Crappies.)
DESIRED result:
Row custno moddate who 1 581827 2012-11-08 08:38:00.000 EMSZC14 2 581827 2012-11-08 08:41:10.000 EMSZC14 3 581827 2012-11-08 08:53:46.000 EMSZC14 4 581827 2012-11-08 08:57:04.000 EMSZC14 5 581827 2012-11-08 08:58:35.000 EMSZC14 6 581827 2012-11-08 08:59:13.000 EMSZC14 7 581827 2012-11-08 09:00:06.000 EMSZC14 1 581827 2012-11-08 09:04:39.000 EMSZC49 Reset row number to 1 2 581827 2012-11-08 09:05:04.000 EMSZC49 3 581827 2012-11-08 09:06:32.000 EMSZC49 4 581827 2012-11-08 09:12:03.000 EMSZC49 5 581827 2012-11-08 09:12:38.000 EMSZC49 6 581827 2012-11-08 09:14:18.000 EMSZC49 1 581827 2012-11-08 09:17:35.000 EMSZC14 Reset row number to 1
Any assistance is appreciated.