CODEWITHSUNDEEP
mysqlsql
Al_
Al_

Reputation: 1511

MySQL query to return last values of a table complying a condition and limited by other condition

I have a table with following structure:

CREATE TABLE `range` (
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
  `f0` float(10,2) NOT NULL,
  `f1` float(10,2) NOT NULL,
  ...

which rows describe ranges of float numbers [f0-f1]. This ranges may be increasing (i.e. f0 < f1) or decreasing (i.e. f0 > f1).

Ordering by ts, I'm searching for a query that returns the last increasing rows up to the first decreasing row. For instance,

+---------------------+------+------+
| ts                  | f0   | f1   |
+---------------------+------+------+
| ...                 | ...  | ...  |
| 2015-01-24 00:00:00 | 0.20 | 0.05 |
| 2015-01-25 00:00:00 | 0.10 | 0.20 |
| 2015-01-26 00:00:00 | 0.10 | 0.30 |
+---------------------+------+------+

the query would return the last two rows, and in case of

+---------------------+------+------+
| ts                  | f0   | f1   |
+---------------------+------+------+
| ...                 | ...  | ...  |
| 2015-01-24 00:00:00 | 0.10 | 0.20 |
| 2015-01-25 00:00:00 | 0.10 | 0.30 |
| 2015-01-26 00:00:00 | 0.20 | 0.05 |
+---------------------+------+------+

it would return 0 rows.

Any thoughts how to limit results based on a condition?

SELECT * FROM range WHERE f0 > f1 ORDER BY ts DESC LIMIT ???

Your help will be much appreciated.

Upvotes: 0

Views: 52

Answers (1)

Neville Kuyt
Neville Kuyt

Reputation: 29629

select *
from   range
where ts > (select max(ts)
            from range
            where f0 > f1)
order by ts

Upvotes: 1

Related Questions