[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 |
+-----+-----+

Answered By – djeison

Answer Checked By – David Marino (BugsFixing Volunteer)