# Find continuous records with equal values characters

partitioning

I need your kind support to resolve this issue.

I have a table contains similar to the below data :

|AccountNo|remarks    |
|Q32571   |Locked     |
|Q32571   |Locked     |


The expected result shall give me the continuous records only with the same remarks

|AccountNo|TotCont|
|Q32571   |    3  |


thanks

As mentioned in the comments, you need to have a way to determine the order of rows, so I added an ID column to my example. I also added another AccountNumber and mixed the data up a bit to verify that my solution would work for other accounts. You did not specify your RDBMS. My solution uses SQL Server.

--Set up demo data
DECLARE @T TABLE
([ID] int,[AccountNumber] varchar(10), [Remarks] varchar(100))
;

insert into @t(ID,AccountNumber,Remarks) values
(2,'Q32571','Locked'),
(3,'Q11111','Locked'),
(4,'Q11111','Locked'),
(8,'Q32571','Locked'),
(11,'Q11111','Locked')
;

--The actual query
;with _groupings as
(
select AccountNumber, Remarks, count(*) as CountConsecutive
from (select t.*,
(row_number() over (order by AccountNumber, ID) -
row_number() over (partition by Remarks order by AccountNumber,ID)
) as grp
from @t t
) t
group by AccountNumber, grp, Remarks
)
select AccountNumber, Max(CountConsecutive) as CountConsecutive
from _groupings
group by AccountNumber


| AccountNumber | CountConsecutive |
|---------------|------------------|
| Q11111        | 2                |
| Q32571        | 3                |