Let's say I have the following table:
Value Time
0 15/06/2012 8:03:43 PM
1 15/06/2012 8:03:43 PM *
1 15/06/2012 8:03:48 PM
1 15/06/2012 8:03:53 PM
1 15/06/2012 8:03:58 PM
2 15/06/2012 8:04:03 PM *
2 15/06/2012 8:04:08 PM
3 15/06/2012 8:04:13 PM *
3 15/06/2012 8:04:18 PM
3 15/06/2012 8:04:23 PM
2 15/06/2012 8:04:28 PM *
2 15/06/2012 8:04:33 PM
How do I select the starred rows, that is, where Value has changed? Basically I'm trying to find the time when Value has changed so I can do other queries based on those time intervals. The solution shouldn't depend on knowing Value or Time in advance.
It seems to me that this shouldn't be very hard (but it's hard enough for me apparently!).
I'm currently using SQL Server 2008 although I have access to 2012 if the new window/analytic functions are helpful.
I tried adapting the solutions here http://blog.sqlauthority.com/2011/11/24/sql-server-solution-to-puzzle-simulate-lead-and-lag-without-using-sql-server-2012-analytic-function/ but my query didn't complete after an hour! I think the joins explode the row size to something unmanageable (or I screwed it up).
I can solve this problem with C# code and multiple db calls, but it seems like something that could be done in a table-valued function or SP which would be much nicer.
Also, a solution that only works with increasing Value is OK if that is easier.
I think this is what you're after:
;WITH x AS
(
SELECT value, time, rn = ROW_NUMBER() OVER
(PARTITION BY Value ORDER BY Time)
FROM dbo.table
)
SELECT * FROM x WHERE rn = 1;
This may be slow if the resultset is large and there isn't a good supporting index...
EDIT
Ah, wait a second, the values go up and down, not just up... if that is the case you can try this much slower approach:
DECLARE @x TABLE(value INT, [time] DATETIME)
INSERT @x VALUES
(0,'20120615 8:03:43 PM'),--
(1,'20120615 8:03:43 PM'),--*
(1,'20120615 8:03:48 PM'),--
(1,'20120615 8:03:53 PM'),--
(1,'20120615 8:03:58 PM'),--
(2,'20120615 8:04:03 PM'),--*
(2,'20120615 8:04:08 PM'),--
(3,'20120615 8:04:13 PM'),--*
(3,'20120615 8:04:18 PM'),--
(3,'20120615 8:04:23 PM'),--
(2,'20120615 8:04:28 PM'),--*
(2,'20120615 8:04:33 PM');
;WITH x AS
(
SELECT *, rn = ROW_NUMBER() OVER (ORDER BY time)
FROM @x
)
SELECT x.value, x.[time]
FROM x LEFT OUTER JOIN x AS y
ON x.rn = y.rn + 1
AND x.value <> y.value
WHERE y.value IS NOT NULL;
Results:
value time
----- -----------------------
1 2012-06-15 20:03:43.000
2 2012-06-15 20:04:03.000
3 2012-06-15 20:04:13.000
2 2012-06-15 20:04:28.000
DECLARE @x TABLE(value INT, [time] DATETIME)
INSERT @x VALUES
(0,'20120615 8:03:43 PM'),--
(1,'20120615 8:03:43 PM'),--*
(1,'20120615 8:03:48 PM'),--
(1,'20120615 8:03:53 PM'),--
(1,'20120615 8:03:58 PM'),--
(2,'20120615 8:04:03 PM'),--*
(2,'20120615 8:04:08 PM'),--
(3,'20120615 8:04:13 PM'),--*
(3,'20120615 8:04:18 PM'),--
(3,'20120615 8:04:23 PM'),--
(2,'20120615 8:04:28 PM'),--*
(2,'20120615 8:04:33 PM');
; with temp as
(
SELECT
value, [time], lag(value,1,-1) over (order by [time] ) as lastValue
FROM @x
)
SELECT
[value],[time]
FROM
temp
WHERE value <> lastValue
Results:
value time
---------------------------
0 2012-06-15 20:03:43.000
1 2012-06-15 20:03:43.000
2 2012-06-15 20:04:03.000
3 2012-06-15 20:04:13.000
2 2012-06-15 20:04:28.000
We can do this using sub queries also
SELECT sub1.value, sub1.time FROM
(SELECT *,rn,id FROM
(SELECT *,row_number() over (partition by value order by time) AS rn, row_number() over (order by time) AS id FROM x ) order by time) sub1
LEFT OUTER JOIN
(SELECT *,rn,id FROM
(SELECT *,row_number() over (partition by value order by time) AS rn, row_number() over (order by time) AS id FROM x ) order by time) sub2
ON sub1.id = sub2.id + 1
WHERE sub1.rn - sub2.rn <> 1 OR sub2.rn IS NULL;
So, I have compared the values of 2 rows if it changes then the difference of rn will be not equal to 1 otherwise rn value will increment by 1 so, I have picked all the rows whose difference with next row's rn value is not 1 and sub2.rn IS NULL is used for the first row because the join will occur from id = 2.
