Select records where a field is different then the last record

[chessley3]

I have a table that is a record of values over time, because of the way the values are recored when the value is changing that record shows a value somewere between the old and new value

Here is an example.
Time Value1 Value2
1:00 2.5 3
2:00 2.5 3.5
3:00 2.75 4
4:00 3 4


I would like to write a set of queries that will select only the records when the value has just changed. For example from the table above if the query was for value1 it would return
Time Value1 Value2
4:00 3 4

If it was for value2 it would return
Time Value1 Value2
3:00 2.75 4

Any suggestions

 

[lespaul]

why would you want the 4:00 record for value1 changes? The data changed in value1 at the 3:00 record, didn't it?

Are your fields really value1 and value2? having field names that are identical except for numbers at the end are usually a red flag that your table aren't normalized....


Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Golom]

Are you looking for the most recent change in Value1 (or Value2) or do you want to see all records where the value has changed?

Some clarification ...

It looks to me that Value1 has changed from 2:00 to 3.00 and again from 3:00 to 4:00. I don't see any obvious way to distinguish between "in the process of changing" (i.e. 2.5 to 2.75) and "changed" (i.e. 2.75 to 3.00)

 

[BigRed1212]

I thought one couldn't assume record order? I thought that while those may represent sequential observations they could be in any order in the database...

 

[Golom]

BigRed1212

You're right that you can't assume a record order but this problem has time stamp (e.g. 1:00, 2:00, 3:00, etc.) that you can use to impose an order.

 

[chessley3]

Golom:

I'm looking for al records where the value has changed, but only when the change was maintained for a few hours (those records that are "changed" not "in the process of changing")

 

[Golom]

By that definition ... and using your example ... Value1 has not changed at all because each different value after 2:00 has existed for only one hour.

You need some criteria more precise than "... for a few hours ..."

If you can state a rule something like "... a value has changed if it has held the same value for two or more hours ..." then the last change to Value1 was at 2:00 with a value of 2.5 and it is in the process of changing (i.e. different values at 3:00 and 4:00).

Select TOP 1 MAX([Time]) As [Change End Time], Value1

From myTable

Group By Value1

HAVING Count(*) >= 2

Order By 1 DESC

Will give you the last time and value where the value was constant for at least 2 hours.