How to get all records where value is higher than previous value. For example, first range in the table below start on id 1 and ends on id 6, next range is from 7 to 10, etc...
id Open
1 1.30077
2 1.30088
3 1.30115
4 1.30132
5 1.30135
6 1.30144
7 1.30132
8 1.30137
9 1.30152
10 1.30158
11 1.30149
12 ...
YOUR SAMPLE DATA
YOUR SAMPLE DATA LOADED
QUERY USING JOINS
Here is the LEFT JOIN query
Here is its output
QUERY WITHOUT JOINS
Using user-defined variables, you simply monitor each row and see when the previous value is greater. Ready for the query? Here it is:
Here is your sample data tripled:
Here is the query's execution:
The key point is this: Every time a new group number appears, that tells you the the next value dropped.
PLEASE NOTE THE OUTPUT IS IDENTICAL FOR BOTH QUERIES
CAVEAT: The second query is not a perfect solution in case there is some floating point issues between
prev
andopen
. If these are ridiculously close to each other, it may not be right. This was best try outside of writing a stored procedure.