How to Identify SQL Rows Where Column Values Have Changed?

Query Rows with Altered Column Values Using SQL

How can one pinpoint the instances of column value changes within a table? This query allows for the identification of rows where specific column values have undergone modifications.

To illustrate, consider 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     

The objective is to select the rows marked with asterisks, indicating value changes. This is crucial for determining the time intervals associated with those changes, which can be leveraged for other database queries.

Solution:

Implementing a SQL query with window functions and row numbers, we derive the desired rows:

;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;

Explanation:

1. The ROW_NUMBER() function assigns sequential row numbers within each partition based on the Value column, thereby creating groups of identical values.
2. The subsequent WHERE rn = 1 condition selects only the first row within each group, representing the initial occurrence of each distinct value.

Additional Considerations:

• For increasing values only, the solution becomes simpler:

SELECT * FROM table WHERE value <> LAG(value) OVER (ORDER BY time);

• If values fluctuate both up and down, a slightly slower approach is required:

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;

By leveraging the power of SQL's window functions, this query effectively retrieves the rows where column values have changed, providing valuable insights into data fluctuations.

The above is the detailed content of How to Identify SQL Rows Where Column Values Have Changed?. For more information, please follow other related articles on the PHP Chinese website!