I have a table that has an ID, a value, and a date. There are many IDs, Values, and dates in this table.
Records are inserted into this table periodically. The ID will always stay the same but occasionally the value will change.
How can I write a query that will give me the ID plus the most recent time the value has changed? Note: the value will always increase.
From this sample data:
Create Table Taco
( Taco_ID int,
Taco_value int,
Taco_date datetime)
Insert INTO Taco
Values (1, 1, '2012-07-01 00:00:01'),
(1, 1, '2012-07-01 00:00:02'),
(1, 1, '2012-07-01 00:00:03'),
(1, 1, '2012-07-01 00:00:04'),
(1, 2, '2012-07-01 00:00:05'),
(1, 2, '2012-07-01 00:00:06'),
(1, 2, '2012-07-01 00:00:07'),
(1, 2, '2012-07-01 00:00:08')
The result should be:
Taco_ID Taco_date
1 2012-07-01 00:00:05
(Because 00:05 was the last time Taco_Value
changed.)
Best Answer
These two queries rely on the assumption that
Taco_value
always increases over time.An alternative with fewer window function madness:
Examples at SQLfiddle
Update
For those keeping track, there was contention over what happens if
Taco_value
could ever repeat. If it could go from 1 to 2 and then back to 1 for any givenTaco_ID
, the queries will not work. Here is a solution for that case, even if it isn't quite the gaps & islands technique that someone like Itzik Ben-Gan may be able to dream up, and even if it isn't relevant for the OP's scenario - it may be relevant to a future reader. It's a little more complex, and I also added an additional variable - aTaco_ID
that only ever has oneTaco_value
.If you want to include the first row for any ID where value didn't change at all in the entire set:
If you want to exclude those rows, it's a bit more complex, but still minor changes:
Updated SQLfiddle examples