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

|AccountNo|TotCont|
|Q32571   |    3  |

Can anyone help please

thanks

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

insert into @t(ID,AccountNumber,Remarks) values
(1,'Q32571','Actual Read'),
(2,'Q32571','Locked'),
(3,'Q11111','Locked'),
(4,'Q11111','Locked'),
(5,'Q32571','Actual Read'),
(6,'Q32571','Actual Read'),
(7,'Q32571','Actual Read'),
(8,'Q32571','Locked'),
(9,'Q32571','Actual Read'),
(10,'Q11111','Actual Read'),
(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                |