# [SOLVED] Write a SQL query to find all numbers that appear at least three times

## 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 | +-----+-----+```