# Consecutive Numbers

• SELECT *
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num

• @zhouyanghhu I just tried your solution for the data below. it returns duplicated Nums. (1 1 1.). As the editorial solution suggested, 'distinct' is needed.

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

• why?
SELECT s.last_num AS consecutivenums FROM (
SELECT id,IF(@last_num = t.num,@rn:=@rn+1,@rn:=1) col,
(@last_num :=t.num) last_num
FROM LOGS t,(SELECT @last_num :=0,@rn :=1) b) s WHERE s.col>=3;

• That is 3 times.Not at least 3 times.So the answer doesn't perfect?

• I got it.The answer is correct and I was wrong before.How stupy I am!

• Slightly different approach to the outlined answer:

SELECT DISTINCT L1.Num AS ConsecutiveNums
FROM Logs L1
LEFT JOIN Logs L2 ON L2.Id = (L1.Id + 1)
LEFT JOIN Logs L3 ON L3.Id = (L1.Id + 2)
WHERE L1.Num = L2.Num
AND L2.Num = L3.Num

• Another approach using a nested query. I think it is better
set @prev = null;
set @conCount = 0;

select distinct t.Num as ConsecutiveNums from
(select Num,
case
when @prev = Num then @conCount := @conCount + 1
when (@prev := Num) is not null then @conCount := 1
end as ConCount
from Logs
) as t
where t.ConCount = 3

• This approach is wrong when there are more than 3 consecutive number showed up

• Sorry I was wrong

• select a.num as ConsecutiveNums
from (
select count(num) as count1, num
from Logs
group by num
) a
where a.count1 > 3
I got the correct result, but cant submit it ...who can tell me there is any problem with my code???thanks very much

• @JoyJoe The description require the number that appears consecutively for at least three times，not just count it. So you have to solve the problem of consecutiveness.

• @Zhenguo0 OK I know, thanks very much. I will try it again !!!

• select distinct A.Num as ConsecutiveNums
from Logs A, Logs B, Logs C
where B.Num= A.Num
and C.Num = B.Num
and B.Id = A.Id+1
and C.Id = B.Id + 1

• select Num from (select Num,count(Num) as qw from Logs group by Num) qwe where qw>=3
Why is this the wrong answer?

• I misread the question,i am sorry to ask such a silly question

• select case when count(Num)>3 then Num end from Logs

• Do we need two seperate joins , will this work
l1.Id = l2.Id and l1.Id = l2.Id + 1 and l1.Id = l2.Id + 2, this will return three rows for one id that have three consecutive matches... then we can do group by count and having >= 3

• select distinct W as ConsecutiveNums from (
select Num ,
@t:=case when @p1=Num and @p2=Num then Num else -999 end as W,
case when @t <> -999 then @p2:=-2999 else @p2:=@p1 end,
case when @t <> -999 then @p1:=-1999 else @p1:=Num end
from Logs ,(select @t:=0, @p1:=-1999,@p2:=-2999) a
) x
where x.W <> -999

• Select distinct
Case when (Num = (select Num from Logs where ID = L.ID+1) AND Num = (select Num from Logs where ID = L.ID+2))
THEN Num END
from Logs L;

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.