Retrieving Rows with Evolving Column Values
Problem:
You're tasked with extracting rows from a table based on changes in a specific column value. In the provided sample data, the "Value" column undergoes several shifts. Your goal is to identify and select only those rows where this value has changed.
Solution:
Using ROW_NUMBER Partitioning:
This technique utilizes SQL Server's ROW_NUMBER function to assign sequential numbers to rows within distinct "Value" partitions, sorted by "Time" order. The following query applies this concept:
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;
By selecting rows with an "rn" value of 1, we effectively isolate those that mark the first occurrence of a different "Value" in the sequence.
Alternative Slower Approach for Dynamic Values:
If the "Value" column permits both ascending and descending changes, the following query offers a slower, but comprehensive solution:
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;
This query employs a series of JOINs and filters to identify value changes, resulting in a list of times where "Value" altered.
The above is the detailed content of How to Efficiently Retrieve Rows Where a Column Value Changes in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!