Having the below database:
Trying to execute below query:
select *
from data_five_minutes
where ((open in (select open
from data_five_minutes
where date_time = '2019-01-02 13:15:00')) >
(open in (select open
from data_five_minutes
where date_time = '2019-01-01 11:10:00')))
The above query gives me result give me 3 results but I was expecting 0!
What I try to fetch is:- get the row where the open of 02/01 13:15 > open of 01/01 11:10 having script_id same for both.
You can use EXISTS
:
SELECT d1.*
FROM data_five_minutes d1
WHERE d1.date_time = '2019-01-02 13:15:00'
AND EXISTS (
SELECT 1
FROM data_five_minutes d2
WHERE d2.date_time = '2019-01-01 11:10:00'
AND d2.script_id = d1.script_id
AND d2.open < d1.open
);