MYSQL 5.6 does not recognize the query as it should

Question:

Greetings!

I'm building a query to get all the times when the value was 0 on a given day and the highest hour of the day before.

The data in my database is similar to what I put in SQL Fiddle . Time_Stamp and Value column. So far, I built this query to perform 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 in my database, but it works in SQL Fiddle. I would like to know what the possible reasons are and how to get around this. I would also like to know, if possible, a more efficient alternative to achieve the desired result.

Thank you in advance.

Answer:

I don't think you need to do a lot of sub-query.

You can use UNION and split your query in 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)
Scroll to Top