Simple solution


  • 55
    L
    Select DISTINCT l1.Num 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

  • 1
    B

    Why do you need 3 Logs?

    What's the problem with

    SELECT DISTINCT l1.Num FROM Logs l1, Logs l2
    WHERE l1.Id+1 = l2.Id AND l1.Id+2 = l2.Id AND l1.Num=l2.Num;
    

    (It doesn't work, but I still want to know why)


  • 7
    J

    l1.Id+1 = l2.Id AND l1.Id+2 = l2.Id can't be true at the same time


  • 1
    H

    Why is it that the above works but this doesn't?

    select distinct l1.Num from
    Logs l1 join Logs l2
    on l1.Id = l2.Id - 1
    join Logs l3
    on l2.Id = l3.Id -1
    where lt.Num = l2.Num = l3.Num

  • 0
    Y

    Say l1.Id is 2 (making l2.Id = 3), then when this row is selected from l1 table it is locked with that row.
    Now, it is impossible to have l1.Id of 1 (since l1.Id+2 = 3) when we have previously selected a row in l1 that corresponds to l1.Id = 2.


  • 0
    Y

    The final where clause is wrong. It really should be l1.Num.


  • 0
    T

    Even if lt.Num is replaced with l1.Num I've never seen a three-way equality operator. I'm no expert in MySQL, but it's usually interpreted like this in other languages: (l1.Num = l2.Num) = l3.Num, so a boolean expression is compared to l3.Num meaning it'll only match if l3.Num is 0 or 1 giving an incorrect solution to this problem.


  • 0
    S

    select Num from Logs group by Num having count(Num) > 3

    why this does not work?
    We just ned to get the numbers that appear atleast 3 times. logically this should also be true


  • 0
    J

    The question asked for consecutive numbers, so you need to look at the Id column to define what "consecutive" means in SQL


  • 1
    J

    Note that this solution will only work assuming we haven't removed any rows


  • 1
    M

    I think three-way equality operator is the problem, if you just them pairwise, the problem will be solved.
    select distinct l1.Num from
    Logs l1 join Logs l2
    on l1.Id = l2.Id - 1
    join Logs l3
    on l2.Id = l3.Id -1
    where l1.Num = l2.Num and l2.Num = l3.Num


  • 0
    X

    My initial try is similar but not as good as this one. However my code works on my local mysql instance, not when I submit.

    SELECT DISTINCT t1.Num FROM Logs AS t1
    WHERE EXISTS (SELECT 1 FROM Logs AS t2 WHERE t2.Id = t1.Id+1 AND t2.Num = t1.Num)
    AND EXISTS (SELECT 1 FROM Logs AS t3 WHERE t3.Id = t1.Id+2 AND t3.Num = t1.Num);
    

    Any suggestion?


  • 0
    N

    It doesnt check for the Num to be equal for the 3rd consecutive row (for which we need another 'Logs l3' such as in your case)


  • 4
    D

    I think the question is not rigorous, the pre-condition is that the id is also consecutive!


  • 1
    H

    it dosen't work


  • 3
    L

    @hbli123 you need add ConsecutiveNums since leetcode has changed the expected name of the output.

    Select DISTINCT l1.Num ConsecutiveNums 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
    

    @lemonxixi maybe you should update your answer.


  • 0
    K

    @bwzhao because l1.Id+1 = l2.Id and l1.Id+2 = l2.Id are returning different records, which means the condition in your where clause won't return any valid records in the table.


  • 0
    H

    @bwzhao WHERE l1.Id+1 = l2.Id AND l1.Id+2 = l2.Id
    this condition will ALWAYS be false


  • 1
    E

    Why the following codes were wrong? Thx

    select L1.Num as ConsecutiveNums 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


  • 0
    R

    In fact, the Id isn't always consecutive.


Log in to reply
 

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