Recursive SQL, have the last three values increased?

Question

Hi,

I have a table that looks like this:

----------------------
| DateTime   | Value |
----------------------
| 2010-01-01 |   26  |
| 2010-02-01 |   24  |       
| 2010-03-01 |   23  |
| 2010-04-01 |   28  |
| 2010-05-01 |   30  |

I need to find if the last three consecutive months have increased in value.

How can I get SQL to check whether the value for each has increased? If anyone can give me any pointers for solving this problem it would be greatly appreciated!

Thanks

Answer 1

With @n standing for one less than the number of consecutive values you want to check (2, in your case):

SELECT MIN(increase) AS increasing
  FROM (SELECT t1.`Value` > t2.`Value` AS increase
          FROM tbl AS t1
            JOIN tbl AS t2
              ON t1.`DateTime` = t2.`DateTime` + INTERVAL 1 month
          WHERE t1.DateTime < '2010-06-01'
          ORDER BY t1.`DateTime` DESC
          LIMIT @n) AS tmp;

or:

SELECT MIN(t1.`Value` > t2.`Value`) AS increasing
  FROM tbl AS t1
    JOIN tbl AS t2
      ON t1.`DateTime` = t2.`DateTime` + INTERVAL 1 month
  WHERE t1.`DateTime` > NOW() - INTERVAL 3 month;