MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tablename (`Id` INTEGER, `pattern` INTEGER);

INSERT INTO tablename (`Id`, `pattern`) VALUES
  ('1', '1'), ('2', '2'),
  ('3', '1'), ('4', '2'), ('5', '3'), ('6', '4'),
  ('7', '1'), ('8', '2'), 
  ('9', '1'), ('10', '2'), ('11', '3');
SELECT Id, pattern
FROM (
  SELECT *, MAX(pattern) OVER (PARTITION BY grp) max_pattern
  FROM (
    SELECT *, SUM(pattern = 1) OVER (ORDER BY Id) grp
    FROM tablename
  ) t
) t
WHERE max_pattern >= 3;


Id
pattern


3
1

4
2

5
3

6
4

9
1

10
2

11
3


SELECT t1.*
FROM tablename t1
WHERE (SELECT t2.pattern FROM tablename t2 WHERE t2.Id = t1.Id + (3 - t1.pattern)) = 3; 


Id
pattern


3
1

4
2

5
3

6
4

9
1

10
2

11
3