How to count consecutive duplicates in a table?

Question

I have a question below:
Want to find successive 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 look at the value of the PG column, and I need the output as 6, which is the number of max consecutive duplicates.

Answer 1

This can be done using the Tabibitosan method. Run this to figure it out:

 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 has been 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 the by with count group to find the group with the 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 the output with the condition where.

where y.rr is not null query returns duplicates

where y.rr is null request returns "different" values.

Answer 3

Just for completeness, here is the actual Tabibitosan method:

 with sample_data as (select 1 slno, 'A1' name, 'NO' pg from dual union all select 2 slno, 'A2' name, 'YES' pg from dual union all select 3 slno, 'A3' name, 'NO' pg from dual union all select 4 slno, 'A4' name, 'YES' pg from dual union all select 6 slno, 'A5' name, 'YES' pg from dual union all select 7 slno, 'A6' name, 'YES' pg from dual union all select 8 slno, 'A7' name, 'YES' pg from dual union all select 9 slno, 'A8' name, 'YES' pg from dual union all select 10 slno, 'A9' name, 'YES' pg from dual union all select 11 slno, 'A10' name, 'NO' pg from dual union all select 12 slno, 'A11' name, 'YES' pg from dual union all select 13 slno, 'A12' name, 'NO' pg from dual union all select 14 slno, 'A14' name, 'NO' pg from dual) -- end of mimicking a table called "sample_data" containing your data; see SQL below: select max(cnt) max_pg_in_queue from (select count(*) cnt from (select slno, name, pg, row_number() over (order by slno) - row_number() over (partition by pg order by slno) grp from sample_data) where pg = 'YES' group by grp); MAX_PG_IN_QUEUE --------------- 6 

Answer 4

 SELECT MAX(consecutives) -- Block 1 FROM ( SELECT t1.pg, t1.slno, COUNT(*) AS consecutives -- Block 2 FROM test t1 INNER JOIN test t2 ON t1.pg = t2.pg WHERE t1.slno <= t2.slno AND NOT EXISTS ( SELECT * -- Block 3 FROM test t3 WHERE t3.slno > t1.slno AND t3.slno < t2.slno AND t3.pg != t1.pg ) GROUP BY t1.pg, t1.slno ); 

The query computes the result as follows:

• Retrieve all pairs of records that do not have a record with a different PG value between (blocks 2 and 3)
• Group them by the PG value and start SLNO value →, this counts consecutive values ​​for any pair [ PG , (start) SLNO ] (block 2);
• Extract Maximum value from query 2 (block 1)

Please note that the query can be simplified if the slno field in the table contains sequential values, but this is not your case (there is no example with SLNO = 5 in your record)

Answer 5

Only one aggregation request is required and there are no joins (the rest of the calculation can be done using ROW_NUMBER , LAG and LAST_VALUE ):

 SELECT MAX( num_before_in_queue ) AS max_sequential_in_queue FROM ( SELECT rn - LAST_VALUE( has_changed ) IGNORE NULL OVER ( ORDER BY ROWNUM ) + 1 AS num_before_in_queue FROM ( SELECT pg, ROW_NUMBER() OVER ( ORDER BY slno ) AS rn, CASE pg WHEN LAG( pg ) OVER ( ORDER BY slno ) THEN NULL ELSE ROW_NUMBER() OVER ( ORDER BY sl_no ) END AS change FROM table_name ) WHERE pg = 'Y' ); 

Answer 6

Try using row_number ()

 select SLNO, Name, PG, row_number() over (partition by PG order by PG) as 'Consecutive' from <table> order by SLNO, NAME, PG 

This should work with a minor tweak.

- EDIT -

Sorry, partiton from PG. The split tells row_number when a new sequence begins.