How to count consecutive duplicates in a table?

Question

I have below question:
Want to find the consecutive duplicates

SLNO   NAME     PG   
1       A1      NO                   
2       A2      YES              
3       A3      NO           
4       A4      YES          
6       A5      YES          
7       A6      YES          
8       A7      YES      
9       A8      YES  
10      A9      YES
11      A10     NO 
12      A11     YES 
13      A12     NO 
14      A14     NO

We will consider the value of PG column and I need the output as 6 which is the count of maximum consecutive duplicates.

Answer 1

It can be done with Tabibitosan method. Run this, to understand it:

with a as(
select 1 slno, 'A' pg from dual union all
select 2 slno, 'A' pg from dual union all
select 3 slno, 'B' pg from dual union all
select 4 slno, 'A' pg from dual union all
select 5 slno, 'A' pg from dual union all
select 6 slno, 'A' pg from dual 
)
select slno, pg, newgrp, sum(newgrp) over (order by slno) grp
from( 
    select slno, 
           pg, 
           case when pg <> nvl(lag(pg) over (order by slno),1) then 1 else 0 end newgrp
    from a
    );

Newgrp means a new group is found.

Result:

SLNO PG NEWGRP GRP
1    A  1      1
2    A  0      1
3    B  1      2
4    A  1      3
5    A  0      3
6    A  0      3

Now, just use a group by with count, to find the group with maximum number of occurrences:

with a as(
select 1 slno, 'A' pg from dual union all
select 2 slno, 'A' pg from dual union all
select 3 slno, 'B' pg from dual union all
select 4 slno, 'A' pg from dual union all
select 5 slno, 'A' pg from dual union all
select 6 slno, 'A' pg from dual 
),
b as(
select slno, pg, newgrp, sum(newgrp) over (order by slno) grp
from( 
    select slno, pg, case when pg <> nvl(lag(pg) over (order by slno),1) then 1 else 0 end newgrp
    from a
    )
)
select max(cnt)
from (
    select grp, count(*) cnt
    from b
    group by grp
    );

Answer 2

with test as (
select 1 slno,'A1' name ,'NO' pg from dual union all 
select 2,'A2','YES' from dual union all
select 3,'A3','NO' from dual union all
select 4,'A4','YES' from dual union all
select 6,'A5','YES' from dual union all
select 7,'A6','YES' from dual union all
select 8,'A7','YES' from dual union all
select 9,'A8','YES' from dual union all
select 10,'A9','YES' from dual union all
select 11,'A10','NO' from dual union all
select 12,'A11','YES' from dual union all
select 13,'A12','NO' from dual union all
select 14,'A14','NO' from dual),
consecutive as (select row_number() over(order by slno) rr, x.* 
              from test x)
select x.* from Consecutive x
  left join Consecutive y on x.rr = y.rr+1 and x.pg = y.pg
  where y.rr is not null
  order by x.slno 

And you can control output with condition in where.

where y.rr is not null query returns duplicates

where y.rr is null query returns "distinct" values.