How to Efficiently Retrieve Rows Where a Column Value Changes in SQL Server?

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!