SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

T-SQL - Finding Changes

Someone posted a note recently asking about how to detect the changes in a sequence. Say you have a table like this:

DECLARE @t_temp table (ID INT IDENTITY(1,1), Value VARCHAR(20))
insert into @t_temp
select 'Win32' UNION ALL
select 'Win32' union ALL
select 'Win32' union ALL
select 'Win32' union ALL
select 'Win32' union ALL
select 'Win32' union ALL
select 'Win64' union ALL
select 'Win64' union ALL
select 'Win32' union ALL
select 'Win32' union ALL
select 'Win64' UNION ALL
select 'Win64'
SELECT * FROM @t_temp

This gives you a simple table and it has these values. Note the first column is just a row counter from the client, the ID and Value columns contain the data.


If you wanted to detect when the Value changes, you’d be looking for the rows with IDs 7, 9, and 11. A simple query gets you that.

select t1.ID, t1.Value
from @t_temp t1
join @t_temp t2
on t2.ID = t1.ID - 1
where t1.Value <> t2.Value

This easily gets those values. However identities aren’t guaranteed to be sequences. What if I deleted row 11 and added another row.

If I add in this code:

INSERT @t_temp SELECT 'Win64'


And I run the query, I get 7, 9 on my system. A better way to do this is to actually use a subquery to find the previous value in the sequence, not assume it’s one less. Here’s an easy way to do this

select t1.ID, t1.VALUE
from @t_temp t1, @t_temp t2
where t1.id > t2.id
and t1.Value <> t2.VALUE
AND t2.id = (SELECT MAX( id)
FROM @t_temp t3
WHERE t3.id < t1.ID

In that subquery, I add a third instance of the table, and I look for the largest ID value that is less than what I’m using as my “current” row in t1. This allows me to find a 10 in t3, and match that to t2 when t1 has the value 12, the next value above 10.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by David Simpson on 21 June 2010

Excellent reminder on not being dependent on the sequencing of an ID column. You could also use a CTE with the ROW_NUMBER function to remove the sub-query and use something like your original query.  Another benefit of the CTE is that you could order by any other column(s) you liked.

WITH temp (rownumber, ID, value)


(SELECT row_number() OVER (ORDER BY id ASC) as rownumber, id, value

FROM @t_temp)

SELECT t1.ID, t1.Value

FROM temp t1

INNER JOIN temp t2

ON t2.rownumber = t1.rownumber - 1

WHERE t1.Value <> t2.Value

Posted by Steve Jones on 21 June 2010

Thanks, I thought this was interesting. It was a good reminder for me to practice things. Good suggestion on the CTE.

Posted by rahul.vashu on 25 June 2010


I like that solution.

Rahul  Sharma

Leave a Comment

Please register or log in to leave a comment.