## Issue

I’m practicing SQL language and got a question like:

Write a SQL query to find all numbers that appear at least three times consecutively.

```
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
```

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

I got a solution online and test it. But I really do not understand it. The big

picture of the solution is clear. `sq`

table counts the occurrences. But I did not understand the part of computing the `sq`

. I’ve done a lot of research on the MYSQL. `@counter := IF(@prev = Num, @counter + 1, 1)`

means if prev = Num, making counter = counter + 1, otherwise counter = 1. `(SELECT @counter:=1, @prev:=NULL) vars`

means create a table `vars`

which includes two columns `counter`

and `pre`

.

Can anyone help me explain the logic of the `sq`

part? Or is there any tutorial for this kind of expressions in the `SELECT`

? I’m totally new to SQL and I know this question may be pretty simple. Thanks for your help!

```
SELECT DISTINCT(Num) AS ConsecutiveNums
FROM (
SELECT
Num,
@counter := IF(@prev = Num, @counter + 1, 1) AS how_many_cnt_in_a_row,
@prev := Num
FROM Logs y, (SELECT @counter:=1, @prev:=NULL) vars
) sq
WHERE how_many_cnt_in_a_row >= 3
```

## Solution

First, the line below is simply initializing variables `@counter`

and `@prev`

. For more information about it check User Defined Variables.

```
(SELECT @counter:=1, @prev:=NULL)
```

So, `sq`

is not an actual table, but it works as an alias so you can reference these in-memory variables.`@counter`

variable counts how many numbers are in consecutive order, when the previous number `@prev`

is different from the actual one `Num`

, `@counter`

is reset to 1 and the counting process starts again.

In order to be clearer, here is what would be the values for `sq`

:

`+-----+-----------------------+`

| Num | how_many_cnt_in_a_row |

+-----+-----------------------+

| 1 | 1 |

| 1 | 2 |

| 1 | 3 |

| 2 | 1 |

| 1 | 1 |

| 2 | 1 |

| 2 | 2 |

+-----+-----+

Answered By – djeison

Answer Checked By – David Marino (BugsFixing Volunteer)