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=...

Bill Karwin · Accepted Answer · 2019-03-19T13:44:23.997

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.

katbarahona · Answer 2 · 2019-03-19T13:52:17.507

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

Do mean this feature is not supported until 8.0, or (as you have it now), this feature is being removed in 8.0? — halfer, Mar 25 '19 at 11:16

Mikhail Vladimirov · Answer 3 · 2019-03-19T13:39:28.643

0

Your query returns zero rows because WHERE condition is always false.

Lets rewrite is as following:

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

You require a.id to be the same as b.id, but at the same time datediff(a.recorddate, b.recorddate) = -1 and b.temp > a.temp which is obviously impossible.

edited Mar 19 '19 at 13:39

answered Mar 19 '19 at 13:35

Mikhail Vladimirov

13,572
1
38
40

And nor does this – Strawberry Mar 19 '19 at 13:38

Joakim Danielson · Answer 4 · 2019-03-19T13:49:02.297

I see that question has been updated (or I missed that part when I read it) so here is my updated answer:

Your query tries to find two rows that has the same id but different values for date and temperature which is of course impossible given that id is unique. Id can't simply be part of an "is equal" condition.

Old answer and a possible way to do it.

Use a simple join based on temperature comparison and DATEDIFF being one between two rows

SELECT *
FROM weather w
JOIN weather w2 ON w.temp > w2.temp AND DATEDIFF( w.recorddate, w2.recorddate) = 1

score 0 · Answer 5 · answered Mar 19 '19 at 13:43

0

Use it:it work for me

Select recorddate from weather where temp>
(Select temp from weather where id =
(Select max(id) from weather)-1)

answered Mar 19 '19 at 13:43

esnkrimi

140
1
8

score -1 · Answer 6 · answered Mar 19 '19 at 13:37

You need to first get the record of the previous date by joining the same table with that date and then apply the temp condition:

select w.recorddate from weather w
join weather w2 on ( w.recorddate = DATE_ADD( w2.recorddate, INTERVAL 1 DAY ) )
where w.temp > w2.temp;

How to select a consecutive date in SQL

Update

Update

6 Answers6