Find continuous records with equal values characters


I need your kind support to resolve this issue.

I have a table contains similar to the below data :

|AccountNo|remarks    |
|Q32571   |Actual Read|
|Q32571   |Locked     |
|Q32571   |Actual Read|
|Q32571   |Actual Read|
|Q32571   |Actual Read|
|Q32571   |Locked     |
|Q32571   |Actual Read|

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

|Q32571   |    3  |

Can anyone help please


Best Answer

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
    ([ID] int,[AccountNumber] varchar(10), [Remarks] varchar(100))

insert into @t(ID,AccountNumber,Remarks) values
(1,'Q32571','Actual Read'),
(5,'Q32571','Actual Read'),
(6,'Q32571','Actual Read'),
(7,'Q32571','Actual Read'),
(9,'Q32571','Actual Read'),
(10,'Q11111','Actual Read'),

--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                |