How to select a consecutive date in SQL

Question

Here is the data that I want to work with

create table weather(
    id int,
    recorddate date,
    temp int,
    primary key (id)
);

insert into weather values (1, '2015-01-01', 10);
insert into weather values (2, '2015-01-02', 15);
insert into weather values (3, '2015-01-03', 20);

I want to select a date that has a higher temp than the previous day, and I used this query:

select id
from weather a
where id = (select id from weather b where datediff(a.recorddate, b.recorddate) = -1 and b.temp > a.temp)

The query returns 0 record and I know the logic of the subquery is correct, but for some reason it does not work.

Update

I am not looking for an alternative way of writing this query, I want to know what is wrong with the query above?

Update

The part that I got wrong is that I was thinking to assign a value to id by writing where id=...

Answer 1

I don't understand why the way I wrote it does not work

Because you're comparing a.id = b.id, but your conditions guarantee they will never be equal.

Here's a demo showing the rows you probably intend to be matched because they have datediff = -1, and b.temp > a.temp, but in both cases, the id's are different.

mysql> select a.id as a_id, b.id as b_id, 
  datediff(a.recorddate, b.recorddate) as datediff, 
  b.temp > a.temp, a.id = b.id 
from weather a cross join weather b;
+------+------+----------+-----------------+-------------+
| a_id | b_id | datediff | b.temp > a.temp | a.id = b.id |
+------+------+----------+-----------------+-------------+
|    1 |    1 |        0 |               0 |           1 |
|    2 |    1 |        1 |               0 |           0 |
|    3 |    1 |        2 |               0 |           0 |
|    1 |    2 |       -1 |               1 |           0 | <--
|    2 |    2 |        0 |               0 |           1 |
|    3 |    2 |        1 |               0 |           0 |
|    1 |    3 |       -2 |               1 |           0 |
|    2 |    3 |       -1 |               1 |           0 | <--
|    3 |    3 |        0 |               0 |           1 |
+------+------+----------+-----------------+-------------+

The only way a.id = b.id is if you're comparing the exact same row (id is the primary key, therefore only one row can have that value), but in those cases, the datediff will naturally be 0 and neither temp will be greater than the other — they'll be equal, because it's the same row.

Answer 2

I recommend you to use the LAG function, using it you can get the temperature of the previous day and then just add a where clause comparing the actual temperature of the row with the result of the LAG function. Here's a good example about it's use: http://www.mysqltutorial.org/mysql-window-functions/mysql-lag-function/. For example:

SELECT id
FROM (
SELECT id, temp, LAG(temp,1) OVER (ORDER BY recorddate ASC) AS prev_temp, 
LAG(recorddate,1) OVER (ORDER BY recorddate ASC) AS prev_recorddate
FROM weather
)
WHERE prev_temp < temp
AND datediff(recorddate, prev_recorddate) = -1

Keep in mind this function it's supported until MySQL 8.0, but you can simulate it as shown in this post Simulate lag function in MySQL