Selecting Rows When Value Changes
I have a stock index table named "snp250" as
No Date Open High Low Close Difference
5061 2015-03-31 8527.60 8550.45 8454.15 8491.00 -1.30
5060 2015-03-30 8390.95 8504.55 8380.75 8492.30 150.90
5059 2015-03-27 8396.00 8413.20 8269.15 8341.40 -0.75
5058 2015-03-26 8474.95 8499.45 8325.35 8342.15 -188.65
before
5041 2015-03-02 8953.85 8972.35 8885.45 8956.75 54.90
Few things -
1. The date is unique but not always consistent as it skips weekends.
2. It is not unique and always consistent.
I need query records where the difference changes from negative to positive. therefore queries fetch the record before the difference changes.
My current sql is
select t1.*
from snp250 as t1, snp250 as t2
where(t1.no_id = t2.no_id+1)
AND((t1.difference>=0 AND t2.difference<0)
OR (t1.difference<0 AND t2.difference>0)).
The problem is that requests are missing the first record.
I tried another sql -
select t1.*
from snp250 as t1, snp250 as t2
where(t1.no_id = t2.no_id-1)
AND((t1.difference>=0 AND t2.difference<0)
OR (t1.difference<0 AND t2.difference>0)).
But here he skips the last entry.
Any help would be appreciated.
+3
source to share
2 answers
The following query uses a correlated subquery to get the previous value:
select t.*,
(select t2.difference
from snp250 t2
where t2.date < t.date
order by t2.date desc
limit 1
) as prev_difference
from snp250 t
To get the lines where the difference changes, you can use the MySQL extension for the clause having
by adding:
having prev_difference < 0 and difference > 0
0
source to share