Find a record R1 that exists another record R2 where R2 is recorded in exactly the previous day of R1 and R2 has a lower temperature.
SELECT Id FROM Weather AS W1 WHERE EXISTS (SELECT * FROM Weather AS W2 WHERE W2.Date = DATE_SUB(W1.Date, INTERVAL 1 DAY) AND W2.Temperature < W1.Temperature);
My answer is almost the same but get "Internal Error":
from Weather t
from Weather w
where w.Date = date_sub(t.Date, interval 1 day)
and w.Temperature < t.Temperature);
I tried on my computer (5.6.16 MySQL Community Server with Workbench 6.2), it worked well (4 records in the table just like the question showed).
And I copied your answer to commit, got "Internal Error" too.
I don't know what's wrong.
How about select * in the exists statement? I tried exists(select 1....)but it gave me internal error while select * works well
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.