Menu
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.
938
SQL Server LAG() is a window function that provides access to a row at a specified physical offset which comes before the current row. In other words, by using the LAG() function, from the current row, you can access data of the previous row, or the row before the previous row, and so on.
One way is to start a transaction, select the contents of the row and compare it to what you're going to update it to. If they don't match, then do the update and end the transaction. If they match, rollback the transaction.
In SQL Server, you can create DML triggers that execute code only when a specific column is updated. The trigger still fires, but you can test whether or not a specific column was updated, and then run code only if that column was updated. You can do this by using the UPDATE() function inside your trigger.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
