There is a table table . It has 1 column s1 . Type of values ​​- integers. There is a task: to output those lines whose values ​​go in a row from n number of times. Example: there is a table

Source table

It is necessary to select values ​​from it that go in a row 3 or more times. Those. The result should be as follows:

Result

Tell me, how can this be realized?

  • Possible solution algorithm. 1) We enumerate the records when sorting in ascending order. 2) Using 2 numbered copies, for each record of the first copy we choose such a record from the second one, that the difference of numbers is equal to the difference of values, and we take the maximum and minimum of the second copy when grouping by the first copy. And we select only unique pairs (DISTINCT) of values ​​from the second copy. This gives us ranges of consecutive values ​​(from and to). 3) Having the boundaries of values, we perform the generation of all values ​​of the ranges - based on the reference table or the third copy of the original table. - Akina
  • An alternative option is a stored procedure with 2 cursors. The first one is used to count the number of consecutive values, the second one is to output these values ​​to a temporary table if the counted number has exceeded a specified threshold. Ps. Both methods require. field values ​​to be unique. - Akina
  • @Akina You list common approaches to solving such problems. But for this particular task, they all look unnecessarily cumbersome. One procedure will suffice, which will iterate over the values ​​with the cursor and, according to the conditions, add them to temporary tables. Uniqueness of values ​​is not required. - Deft
  • What determines the order of the values ​​in this column? - Sergey Moiseenko
  • @Deft The second way is exactly what you say. Uniqueness of values ​​is not required. The author in the example has two values ​​s1 = 7. He considers them in response as one. So either he selects unique ones, or he is guided by the physical order of returning values ​​(perhaps with some sorting), modestly keeping silent about how this order is provided. - Akina

1 answer 1

 SELECT t2.s1 FROM ( SELECT t1.id, t1.s1, IF (@max = 1, @max := t1.seq, @max := @max) AS "max1", IF (t1.seq = 1, @max := 1, @max := @max) AS "max2" FROM ( SELECT (SELECT @id := @id + 1) AS "id", s1, IF (s1 = @prev + 1, @seq := @seq + 1, @seq := 1) AS "seq", (SELECT @prev := s1) AS "prev" FROM `table`, (SELECT @id := 0, @seq := 0, @prev := null) AS init ) AS t1, (SELECT @max := 1) AS init ORDER BY t1.id DESC ) AS t2 WHERE t2.max1 >= 3 ORDER BY t2.id

The answer itself was suggested at https://toster.ru/q/453749