Finding all dates where the value changes

[thing3b]

For once, I have no idea where to start on my SQL problem. I have a database table that can be simplified (for this problem) to 3 columns:

Example_ID
Example_DateAndTime
Example_Value

This table currently has > 1,000,000 rows (and at least 10 other columns). The Example_DateAndTime column is different on almost all rows. The Example_Value column is quite often the same (changing less than once a day).

What I would like to do is find all the dates where the value is different to the previous value.

So out of the 1,000,000 rows, if Example_Value has only been changed 5 times, I would only want 6 rows (I would want the very first row as well.)

I can get the first date that each value occurs, but this is not correct. If value is changed from 200 to 500 to 302 to 200 to 302 to 404, there should be 6 rows, even though value may have been changed back to a previous value.

Does anyone have any ideas?

MySQL Version: 5.0.45

[Jakotsu]

Despite I read the entire post many times, I can't finally understand what do you want to achieve.

I don't get this:

...if Example_Value has only been changed 5 times, I would only want 6 rows...

In MySQL terms, what do you mean by has only been changed 5 times? The same Example_ID row has been updated to change its Example_Value 5 times? If this is the case, I can assure you'll never be able to track the amount of times it's changed unless you add a new field to the table.

[thing3b]

Sorry, I agree that could be more clear.

When I say changed, I mean a new row entered with a new value. The table is a log of events, so there is no point altering previous rows.

[Jakotsu]

OK, I understand now but I don't think you'll be able to achieve this with a simple (nor complex) query, you'll have to use some programming as well.

I'm afraid you'll have to return all the rows, and inspect them one by one, storing the dates on an array whenever the row value is different from the previous one. That's the only way I can figure out.