select range when value is higher than in previous value

Question

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

Answer 1

YOUR SAMPLE DATA

USE test
DROP TABLE IF EXISTS rangedata;
CREATE TABLE rangedata
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  open FLOAT
) ENGINE=MyISAM;
INSERT INTO rangedata (open) VALUES
(1.30077),(1.30088),(1.30115),(1.30132),
(1.30135),(1.30144),(1.30132),(1.30137),
(1.30152),(1.30158),(1.30149),
(1.30077),(1.30088),(1.30115),(1.30132),
(1.30135),(1.30144),(1.30132),(1.30137),
(1.30152),(1.30158),(1.30149),
(1.30077),(1.30088),(1.30115),(1.30132),
(1.30135),(1.30144),(1.30132),(1.30137),
(1.30152),(1.30158),(1.30149);

YOUR SAMPLE DATA LOADED

mysql>     USE test
Database changed
mysql>     DROP TABLE IF EXISTS rangedata;
Query OK, 0 rows affected (0.01 sec)

mysql>     CREATE TABLE rangedata
    ->     (
    ->       id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->       open FLOAT
    ->     ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.09 sec)

mysql>     INSERT INTO rangedata (open) VALUES
    ->     (1.30077),(1.30088),(1.30115),(1.30132),
    ->     (1.30135),(1.30144),(1.30132),(1.30137),
    ->     (1.30152),(1.30158),(1.30149),
    ->     (1.30077),(1.30088),(1.30115),(1.30132),
    ->     (1.30135),(1.30144),(1.30132),(1.30137),
    ->     (1.30152),(1.30158),(1.30149),
    ->     (1.30077),(1.30088),(1.30115),(1.30132),
    ->     (1.30135),(1.30144),(1.30132),(1.30137),
    ->     (1.30152),(1.30158),(1.30149);
Query OK, 33 rows affected (0.00 sec)
Records: 33  Duplicates: 0  Warnings: 0

mysql>

QUERY USING JOINS

Here is the LEFT JOIN query

SET @grp = 1;
SELECT A.open prev,(@grp:=@grp+IF(A.open<B.open,1,0)) group_number
FROM rangedata A LEFT JOIN rangedata B ON A.id= B.id+1;

Here is its output

mysql> SELECT A.open prev,(@grp:=@grp+IF(A.open<B.open,1,0)) group_number
    -> FROM rangedata A LEFT JOIN rangedata B ON A.id= B.id+1;
+---------+--------------+
| prev    | group_number |
+---------+--------------+
| 1.30088 |            1 |
| 1.30115 |            1 |
| 1.30132 |            1 |
| 1.30135 |            1 |
| 1.30144 |            1 |
| 1.30132 |            2 |
| 1.30137 |            2 |
| 1.30152 |            2 |
| 1.30158 |            2 |
| 1.30149 |            3 |
| 1.30077 |            4 |
| 1.30088 |            4 |
| 1.30115 |            4 |
| 1.30132 |            4 |
| 1.30135 |            4 |
| 1.30144 |            4 |
| 1.30132 |            5 |
| 1.30137 |            5 |
| 1.30152 |            5 |
| 1.30158 |            5 |
| 1.30149 |            6 |
| 1.30077 |            7 |
| 1.30088 |            7 |
| 1.30115 |            7 |
| 1.30132 |            7 |
| 1.30135 |            7 |
| 1.30144 |            7 |
| 1.30132 |            8 |
| 1.30137 |            8 |
| 1.30152 |            8 |
| 1.30158 |            8 |
| 1.30149 |            9 |
| 1.30077 |            9 |
+---------+--------------+
33 rows in set (0.01 sec)

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:

SET @prev = '0.00000';
SET @grp = 1;
SELECT id,open,(@grp:=@grp+increasing) group_number FROM
(SELECT id,open,IF(@prev<=open,0,1) increasing,(@prev:=open) FROM rangedata) A;

Here is your sample data tripled:

Here is the query's execution:

mysql> SET @prev = '0.00000';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @grp = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id,open,(@grp:=@grp+increasing) group_number FROM
    -> (SELECT id,open,IF(@prev<=open,0,1) increasing,(@prev:=open) FROM rangedata) A;
+----+---------+--------------+
| id | open    | group_number |
+----+---------+--------------+
|  1 | 1.30077 |            1 |
|  2 | 1.30088 |            1 |
|  3 | 1.30115 |            1 |
|  4 | 1.30132 |            1 |
|  5 | 1.30135 |            1 |
|  6 | 1.30144 |            1 |
|  7 | 1.30132 |            2 |
|  8 | 1.30137 |            2 |
|  9 | 1.30152 |            2 |
| 10 | 1.30158 |            2 |
| 11 | 1.30149 |            3 |
| 12 | 1.30077 |            4 |
| 13 | 1.30088 |            4 |
| 14 | 1.30115 |            4 |
| 15 | 1.30132 |            4 |
| 16 | 1.30135 |            4 |
| 17 | 1.30144 |            4 |
| 18 | 1.30132 |            5 |
| 19 | 1.30137 |            5 |
| 20 | 1.30152 |            5 |
| 21 | 1.30158 |            5 |
| 22 | 1.30149 |            6 |
| 23 | 1.30077 |            7 |
| 24 | 1.30088 |            7 |
| 25 | 1.30115 |            7 |
| 26 | 1.30132 |            7 |
| 27 | 1.30135 |            7 |
| 28 | 1.30144 |            7 |
| 29 | 1.30132 |            8 |
| 30 | 1.30137 |            8 |
| 31 | 1.30152 |            8 |
| 32 | 1.30158 |            8 |
| 33 | 1.30149 |            9 |
+----+---------+--------------+
33 rows in set (0.00 sec)

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 and open. If these are ridiculously close to each other, it may not be right. This was best try outside of writing a stored procedure.