homepage Welcome to WebmasterWorld Guest from 67.202.56.112
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Finding all dates where the value changes
thing3b




msg:3724862
 12:28 pm on Aug 17, 2008 (gmt 0)

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




msg:3725002
 5:45 pm on Aug 17, 2008 (gmt 0)

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




msg:3725075
 8:35 pm on Aug 17, 2008 (gmt 0)

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




msg:3725084
 8:58 pm on Aug 17, 2008 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved