Start counting from 1 when new value identified

Question

I need to create a column that will start the count from 1 every time it finds a different value.

Here is an example of my table:

if object_id('tempdb.dbo.#myTable') is not null drop table #myTable
create table #myTable (dates int, flag char)

insert into #myTable values('20181031','V')
insert into #myTable values('20181130','V')
insert into #myTable values('20181231','V')
insert into #myTable values('20190131','F')
insert into #myTable values('20190228','F')
insert into #myTable values('20190331','F')
insert into #myTable values('20190430','F')
insert into #myTable values('20190531','V')
insert into #myTable values('20190630','V')
insert into #myTable values('20190731','V')

select *
from #myTable
order by 1

dates     flag
20181031    V
20181130    V
20181231    V
20190131    F
20190228    F
20190331    F
20190430    F
20190531    V
20190630    V
20190731    V

I need the output to look as follows:

dates      flag cnt
20181031    V   1
20181130    V   2
20181231    V   3
20190131    F   1
20190228    F   2
20190331    F   3
20190430    F   4
20190531    V   1
20190630    V   2
20190731    V   3

I have tried using variations of row_number() with sub-queries, but I could not get it to start counting again when it hits the second set of Vs. I looked at these two solutions, but I could not extrapolate it for my purpose:
Counting rows until where clause condition is saatisfied

Transact-SQL - number rows until condition met

I'm using SQL Server 2012

Thank you

You should post you actual attempt rather than just mention it. — Dale K, Apr 20 '21 at 02:37
@DaleK I've added evidence of research. Regarding accepting answers, I will need to look through them again, but usually I did not see an answer that worked for me. — jmich738, Apr 20 '21 at 02:38
If an answer doesn't work for you, you should comment as to how and why to the author can improve their answer. — Dale K, Apr 20 '21 at 02:39
@DaleK I usually do that, I'll take a look if some are still relevant. Does my question still need to be downvoted? I've updated it with additional information as you've asked. — jmich738, Apr 20 '21 at 03:39
You're assuming it was me... many people expect to see an actual attempt... — Dale K, Apr 20 '21 at 03:40
@DaleK apologies, I did assume it was you. I guess we can leave it at that. — jmich738, Apr 20 '21 at 03:48

score 4 · Accepted Answer · answered Apr 20 '21 at 02:09

This is a type of gaps-and-islands problem. The simplest solution is to use a difference of row numbers to define the groups. Then an additional row number for the column you want:

select t.*,
       row_number() over (partition by flag, seqnum - seqnum_2 order by date) as cnt
from (select t.*,
             row_number() over (order by date) as seqnum,
             row_number() over (partition by flag order by date) as seqnum_2
      from #mytable t
     ) t
order by date;

Here is a db<>fiddle.

Start counting from 1 when new value identified

1 Answers1