MySQL: find IDs with constatnly increasing values

Question

I have the following table:

create table my_table
(
 SubjectID int,
 Date Date,
 Test_Value int
);

insert into my_table(SubjectID, Date, Test_Value)
values
 (1, '2014-01-01', 55),
 (1, '2014-01-05', 170),
 (1, '2014-01-30', 160),
 (2, '2014-01-02', 175),
 (2, '2014-01-20', 166),
 (2, '2014-01-21', 160),
 (3, '2014-01-05', 70),
 (3, '2014-01-07', 75),
 (3, '2014-01-11', 180)

I want to find IDs with constantly increasing Test_Value over time. In this example, only SubjectID 3 satisfies that condition. Could you write the code to find this out? Thanks for your help as always.

Answer 1

SELECT *
FROM my_table o
WHERE NOT EXISTS (
    SELECT null
    FROM my_table t1
    INNER JOIN my_table t2 ON t2.Date > t1.Date AND t2.Test_Value < t1.Test_Value AND t1.SubjectID = t2.SubjectID
    WHERE t1.SubjectID = o.SubjectID
)

The inner query would select all the entities that DO VIOLATE the requirements: they have later dates with least values. Then the outer select entities that do not match ones from the inner query.

SQLFiddle: http://www.sqlfiddle.com/#!2/1a7ba/12

PS: presumably if you only need an id - use SELECT DISTINCT SubjectID