I have below table
create table #t (Id int, Name char)
insert into #t values
(1, 'A'),
(2, 'A'),
(3, 'B'),
(4, 'B'),
(5, 'B'),
(6, 'B'),
(7, 'C'),
(8, 'B'),
(9, 'B')
I want to count consecutive values in name column
+------+------------+
| Name | Repetition |
+------+------------+
| A | 2 |
| B | 4 |
| C | 1 |
| B | 2 |
+------+------------+
The best thing I tried is:
select Name
, COUNT(*) over (partition by Name order by Id) AS Repetition
from #t
order by Id
but it doesn't give me expected result
One approach is the difference of row numbers:
select name, count(*)
from (select t.*,
(row_number() over (order by id) -
row_number() over (partition by name order by id)
) as grp
from t
) t
group by grp, name;
The logic is easiest to understand if you run the subquery and look at the values of each row number separately and then look at the difference.
I have use Recursive CTE and minimise the use of row_number,also avoid count(*).
I think it will perform better,but in real world it depend what else filter you put to minimise number of rows affected.
If ID is having discreet values then One extra CTE will be use to generate continuous id.
;With CTE2 as
(
select ROW_NUMBER()over(order by id) id, name,1 Repetition ,1 Marker from @t
)
, CTE as
(
select top 1 cast(id as int) id, name,1 Repetition ,1 Marker from CTE2 order by id
union all
select a.id, a.name
, case when a.name=c.name then Repetition +1 else 1 end
, case when a.name=c.name then c.Marker else Marker+1 end
from @t a
inner join CTE c on a.id=c.id+1
)
,CTE1 as
(select *,ROW_NUMBER()over(partition by marker order by id desc)rn from cte c
)
select Name,Repetition from cte1 where rn=1
