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

Question

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

Brigadier

Answer 1

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

Fibrilla

THanks. THis is exactly what I want. Actually, I also just figured it out by myself. see sqlfiddle.com/#!9/e3616/2 – Brigadier

Answer 2

Let's go through each record and see how this query works. It's well written.

SELECT...FROM

What does SELECT...FROM Logs y, (...) vars mean?

If you had a table like this: create table test(field1 int) that contains 3 rows like so:

field1
-------
1
2
3

Doing select * from test, (select @counter:=1, @prev:=NULL) vars will result in

field1  @counter:=1  @prev=NULL
------- ------------ -----------
1       1            NULL
2       1            NULL
3       1            NULL

@counter and @prev are session variables. They are initialized to 1 and NULL respectively. All rows are combined with these variables to give you what you see above.

Row by row analysis of the subquery

Focus on just this subquery.

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

The query selects the first row of ID=1, Num=1, and chooses Num as it's first column.

For the 2nd column, it does some math. It checks if @prev = Num. Well, @prev is NULL because that's how it was initialized. So, @prev = Num results in false. IF is generally written as IF(condition, what-to-do-if-condition-is-true, what-to-do-if-condition-is-false).

IF(@prev = Num, @counter + 1, 1)
   -----------  ------------  --
   condition    do this       do this if condition
                if true       is false

Since @prev is NULL and not equal to Num, 1 is returned.

For the 3rd column, the query just resets @prev to Num. That's really all. Now let's see how SELECT goes line by line and does its magic.

Num  @prev was  @counter was  @counter calculation      @prev reset to Num
---  ---------  ------------  -----------------------   ------------------
1    NULL       1             is @prev = 1? No. So 1      1
1    1          1             is @prev = 1? Yes! So 2     1
1    1          2             is @prev = 1? Yes! So 3     1
2    1          3             is @prev = 2? No. So 1      2
1    2          1             is @prev = 1? No. So 1      1
2    1          1             is @prev = 2? No. So 1      2
2    2          1             is @prev = 2? Yes! So 2     2

The 2nd and 3rd column above are for understanding purposes only.

Now that the subquery has done its job, SELECT DISTINCT... comes and asks: From the result above, give me only the row that has @counter of 3 or higher. The result is going to be

Num   @counter  @prev
----  --------  -----
1     3         1

If your dataset had five 1s one after the other, 3rd, 4th and 5th 1 will be retrieved. Therefore, DISTINCT(Num) is used to select only a single 1. It's just smart thinking. It may be possible to change the WHERE clause to read WHERE ... = 3 insted of >= 3.

Hope this makes sense.

Demark

Thanks so much for your help! It's hard for me to choose which one as the correct answer since @djeison also gives the correct idea and earlier (but not as detail as your). I always do not figure out the rule at stackoverflow for the correct answer. The first almost correct one get the mark or the absolute correct and more detailed one. – Brigadier

You chose the right answer as the correct answer. djeison answered 14 minutes ahead of me and is a meaningful, correct answer. I added my detailed note for reference. So keep djeison's post as the answer - that's my recommendation. – Demark

This is simply amazing. – Aristides

Answer 3

SELECT 
    distinct A.num as ConsecutiveNums 
FROM
    LOGS A
INNER JOIN Logs B
    on A.id=b.id+1
INNER join Logs C
    on b.id=c.id+1
WHERE a.num=b.num AND a.num=c.num
;

Ravo

Please format the SQL with the code tags. Also you should explain what your query exactly does. – Covarrubias

Its also good to give some idea of your DDL Scripts and also Data use something like sqlfiddle.com to provide a minimal reproducible example as per rules. stackoverflow.com/help/minimal-reproducible-example – Gunderson

Answer 4

Try the following query:

select ConsecutiveNums from(    
        select 
        case 
        when lag(Num) over (order by Id) = Num and Num=lead(Num) over (order by 
        Id)  then Num 
        end as ConsecutiveNums
        from Logs
        )  
where ConsecutiveNums is not null

Trujillo

Code only answers are more helpful if you add an explanation – Sortie

Answer 5

Here is another version of the answer...

select distinct num 
  from ( 
          select num
                ,lag(num,1) over() as lag1
                ,lag(num,2) over() as lag2
             from
                 logs
       ) as a
   where  num = lag1 = lag2
     and  lag1 = lag2

Noblesse

when come negative values it give null – Grudging

Answer 6

SELECT DISTINCT Num
FROM (
  SELECT Num,
         LAG(Num) OVER (ORDER BY Id) AS prev_num,
         LEAD(Num) OVER (ORDER BY Id) AS next_num
  FROM your_table
) AS subquery
WHERE Num = prev_num AND Num = next_num;

Nineteenth

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Manna

Answer 7

select distinct num from ( select num,
                            lag(num,1) over(order by ID) lag1,
                            lead(num,1) over(order by ID) lead1
                            from Table
                            ) as a
    where num=lag1 and num=lead1
    and lag1=lead1

Dimitris

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Manna

Answer 8

Another solution to check if next ids have the same value as current id

SELECT DISTINCT Num as ConsecutiveNums
FROM Logs
WHERE (Id + 1, Num) IN (SELECT * FROM Logs) AND (Id + 2, Num) IN (SELECT * FROM Logs)

Ransdell

Answer 9

Another approach

WITH x AS (
    SELECT a.id, a.num
    FROM Logs AS a
    INNER JOIN Logs AS b ON
        a.num = b.num AND 
        b.id - a.id = 1
)
SELECT DISTINCT m.num AS ConsecutiveNums
FROM (
    SELECT x.num
    FROM x
    INNER JOIN x AS y ON
        x.num = y.num AND
        y.id - x.id = 1
) AS m;

Berkey