highest consecutive values in a column SQL-CodePudding

I have below table

create table test (Id int, Name char);

insert into test values
(1, 'A'),
(2, 'A'),
(3, 'B'),
(4, 'B'),
(5, 'B'),
(6, 'B'),
(7, 'C'),
(8, 'B'),
(9, 'B');

I want to print the Name that appears consecutively for at least four times Expected Output:

Name
B

I have tried in different ways similar to below SQL (resulted in two values B & C) but nothing worked.

My sql attempt:

select Name from 
(select t.*, row_number() over (order by Id asc) as grpcnt, 
row_number() over (partition by Name order by Id) as grpcnt1 from t) test 
where (grpcnt-grpcnt1)>=3 
group by Name,(grpcnt-grpcnt1) ;

CodePudding user response：

Try removing the where clause and applying your filter in a having clause based on the counts. Moreover, since you are interested in at least four times, your filter should be >=4. See eg using your modified query:

select 
    Name 
from (
    select 
        *, 
        row_number() over (order by Id asc) as grpcnt,
        row_number() over (partition by Name order by Id) as grpcnt1 
    from test
) t
group by Name,(grpcnt-grpcnt1)
HAVING COUNT(Name)>=4;

View working demo on db fiddle

CodePudding user response：

If your id is your counter you can do this:

select * 
from test t 
where exists (
    select count(*) 
    from test 
    where name='B' 
        and id <= t.id and id > (t.id - 4) 
    having count(*) = 4
);