I posted an updated question on [mysql query, return rows when condition is satisfied](https://stackoverflow.com/questions/27279891)

Thanks to those who helped...

Ok, I'm taking a second stab at this, because I messed up my original question. I've corrected the date format to match mysql. In addition, I actually was derviving the state value in a query based on percentage of the value, i.e. if it was within 85% of highest observed value it gets marked as high, if its within 15% of Low watermark then its marked Low.

Its format is name, test\_date, score.

So something like

```
John, 2014-10-12, 87
John, 2014-10-13, 87
John, 2014-10-14, 33
John, 2014-10-15, 32
John, 2014-10-16, 44
John, 2014-10-17, 87
John, 2014-10-18, 89
John, 2014-10-19, 90
Jane, 2014-10-12, 32
Jane, 2014-10-13, 87
Jane, 2014-10-14, 33
Jane, 2014-10-15, 88
Jane, 2014-10-16, 44
Jane, 2014-10-17, 87
Jane, 2014-10-18, 89
Jane, 2014-10-19, 90

```

Here is the query I currently have to return data that is only within high or low watermark. 

`select userdata.name, test_date, score, if(score >=.85*temp.High,"High","Low") 
from userdata 
inner join (select name, Min(score) as Low, Max(score) as High 
from userdata where (test_date between '2013-12-02' and '2014-12-01') group by name) 
as temp on userdata.name=temp.name where (score >= .85*temp.High or score <= 1.15*temp.Low)
and test_date between '2013-12-02' and '2014-12-01' order by name, test_date`

Assuming highest was 96 and lowest was 35 this returns data such as

```
 John, 2012-12-02, 90, High
 John, 2012-12-03, 40, Low
 John, 2012-12-04, 41, Low
 John, 2012-12-05, 95, High
 John, 2012-12-05, 94, High
 John, 2012-12-02, 90, High
 Jane, 2012-12-03, 40, Low
 Jane, 2012-12-04, 41, Low
 Jane, 2012-12-05, 41, Low 
 Jane, 2012-12-06, 41, Low
 Jane, 2012-12-06, 41, Low
 Jane, 2012-12-11, 89, High

```

Now I'd like to see if I can modify my query to only return the state of changes. I looked at the answers provided, but I'm out of my element in combining them with my original query to produce the desired results. Thanks for the help already given, and I apologize for not phrasing my question correctly the first time.