MYSQL 5.6 does not recognize query as it should



  • Greetings!

    I'm building a query to pick up every time the value was at 0 on a given day and the biggest time of the previous day.

    The data in my bank are similar to the ones I put in http://www.sqlfiddle.com/#!9/ab1926/4 . Time_Stamp and Value Column. To date, I have built this query to carry out the desired query:

        SELECT 
        T1.Time_stamp,
        CASE
            WHEN T2.Time_Stamp THEN T2.Time_Stamp
        END Time_stamp,
        CASE
            WHEN (T2.Valor = 0) THEN T2.Time_Stamp
            WHEN (T1.Valor = 0) THEN T1.Time_Stamp
        END AS T2
    FROM
        (SELECT 
            Time_Stamp,
                Valor,
                @seq1:=@seq1 + 1 AS Seq
        FROM
            test, (SELECT @seq1:=0) r
        WHERE
            CAST(time_stamp AS DATE) = '2018-11-02'
    
    ORDER BY Time_Stamp DESC) T1
        LEFT JOIN
    (SELECT 
        Time_Stamp, Valor, 
            @seq3:=@seq3 + 1 AS Seq
    FROM
        test, (SELECT @seq3:=0) r
    WHERE
        valor = 0.0  and CAST(time_stamp AS DATE) = '2018-11-01'
    ORDER BY TIME_STAMP DESC
    LIMIT 0,1) T2 
    ON T1.Seq = T2.Seq
    

    WHERE
    T1.valor = 0

    The reason I'm writing this question is that this same query doesn't work on my bank, but it works on SQL Fiddle. I'd like to know what reasons are and how to get around that. I would also like to know, if possible, some more efficient alternative to achieve the desired result.

    Thank you.



  • I don't think you need to get too much sub-query.

    You can use UNION and split your query into two so you don't have to reinvent the wheel

    mysql> set @date = '2018-11-02';
    Query OK, 0 rows affected (0.03 sec)
    

    mysql> select * from test where valor = 0
    and (DATE(Time_Stamp) = @date)
    UNION ALL
    (SELECT * from test where valor = 0
    AND DATE(Time_Stamp) = @date - interval 1 day
    ORDER BY Time_Stamp DESC LIMIT 1);

    +---------------------+-------+
    | Time_Stamp | valor |
    +---------------------+-------+
    | 2018-11-02 04:37:59 | 0 |
    | 2018-11-02 04:07:59 | 0 |
    | 2018-11-02 03:29:04 | 0 |
    | 2018-11-01 23:36:40 | 0 |
    +---------------------+-------+
    4 rows in set (0.03 sec)




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2